Delete rolling 12 months records (1 Viewer)

[aman]

Hi guys,

I am trying to delete rolling 12 months records from the table. Can you please amend the query accordingly?

Dim sSql As String
sSql = "delete from tblReturntowork where Datecreated=#" & Format(Date, "mm/dd/yyyy") & "#"
MsgBox sSql

Thanks

 

[Cronk]

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

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

 

[Ranman256]

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

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]

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

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

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