Delete rolling 12 months records (1 Viewer)

aman

Registered User.
Local time
Today, 03:40
Joined
Oct 16, 2008
Messages
1,250
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
 

Cronk

Registered User.
Local time
Today, 21:40
Joined
Jul 4, 2013
Messages
2,770
If you are not worried about leap years ie not precisely records


Code:
....<=#" & Format(Date - 365, "mm/dd/yyyy") & "#"
 

Ranman256

Well-known member
Local time
Today, 06:40
Joined
Apr 9, 2015
Messages
4,337
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)
 

isladogs

MVP / VIP
Local time
Today, 10:40
Joined
Jan 14, 2017
Messages
18,186
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

Top Bottom