Delete rolling 12 months records

aman

Registered User.
Local time
Yesterday, 17:25
Joined
Oct 16, 2008
Messages
1,251
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
 
If you are not worried about leap years ie not precisely records


Code:
....<=#" & Format(Date - 365, "mm/dd/yyyy") & "#"
 
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)
 
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
 

Users who are viewing this thread

Back
Top Bottom