Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-08-2019, 02:52 AM   #1
aman
Newly Registered User
 
Join Date: Oct 2008
Posts: 1,250
Thanks: 54
Thanked 3 Times in 2 Posts
aman is an unknown quantity at this point
Delete rolling 12 months records

Hi guys,

I am trying to delete rolling 12 months records from the table. Can you please amend the query accordingly?
Code:
Dim sSql As String
sSql = "delete from tblReturntowork where Datecreated=#" & Format(Date, "mm/dd/yyyy") & "#"
MsgBox sSql
Thanks

aman is offline   Reply With Quote
Old 02-08-2019, 09:00 AM   #2
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,165
Thanks: 3
Thanked 472 Times in 465 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Delete rolling 12 months records

If you are not worried about leap years ie not precisely records


Code:
....<=#" & Format(Date - 365, "mm/dd/yyyy") & "#"
Cronk is offline   Reply With Quote
Old 02-08-2019, 09:01 AM   #3
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,378
Thanks: 0
Thanked 747 Times in 732 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Delete rolling 12 months records

make a form with a textbox for the date to pull data with: txtCurrDate
another textbox to calculate the previous 12 months: txtPrevDate

Code:
sub txtCurrDate_afterupdate()
  txtPrevDate = DateAdd("m",-12,txtCurrDate)
end sub
Q1,qsCurrDate, would pull data fromtxtCurrDate on
Q2, qdDelOld12, would delete prev 12 mos.

qdDelOld12:
delete * from tblReturnToWork where [Datecreated] between forms!myForm!txtPrevDate and (forms!myForm!txtCurrDate-1)

Ranman256 is offline   Reply With Quote
Old 02-08-2019, 09:18 AM   #4
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,370
Thanks: 115
Thanked 3,111 Times in 2,829 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Delete rolling 12 months records

There are lots of methods available.
Two have already been suggested.
Here's another two with the results in UK format

Code:
DateAdd("yyyy", -1, Date)
08/02/2018 

DateSerial(Year(Date)-1,Month(Date),Day(Date))
08/02/2018

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Count total absences in rolling 12 months aman Modules & VBA 3 08-22-2016 05:43 AM
Rolling 12 months Query DataChick Queries 2 04-01-2010 10:39 PM
Rolling a Date Back 12 Months Question rmoreno Queries 3 03-15-2006 07:32 AM
Delete records 6 months old kbrooks Queries 3 07-18-2002 10:13 AM
Rolling 12 months PaulJK Queries 1 07-10-2002 12:14 PM




All times are GMT -8. The time now is 03:30 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World