Delete rolling 12 months records (1 Viewer)

aman

Registered User.
Local time
Today, 08:54
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
Tomorrow, 01:54
Joined
Jul 4, 2013
Messages
2,383
If you are not worried about leap years ie not precisely records


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

Ranman256

Registered User.
Local time
Today, 11:54
Joined
Apr 9, 2015
Messages
3,576
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

CID Moderator
Staff member
Local time
Today, 16:54
Joined
Jan 14, 2017
Messages
13,421
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 (Users: 0, Guests: 1)

Top Bottom