How to delete record based on date?

Question

For example: I have a table UserTable , which has two fields: UserID and LastActivateDate

I would like to delete records which have not activated for two years. I have other questions in below underline part. I am new to Access VBA, so my code only shows what I would like to do, it may be wrong.

Thanks.

Private Sub DeleteUser()



Dim dbs As Database


Set dbs = CurrentDb
    


dbs.Execute "Delete * from UserTable where Today - LastActivateDate > 730"
'How to write LastActivateDate is two years ago, it does not have to be exact 2 years, 730 days is fine. I am asking how to write the code after WHERE

'Do I need some code to save UserTable or save Access file?

Me.Refresh


End Sub

Answer 1

Hi VA_er,

Try....

Private Sub DeleteUser()

Dim strSQL As String, strDayCount As String

strDayCount = InputBox("How Many Days", "Enter Count")

strSQL = "DELETE UserTable.LastActivateDate FROM UserTable WHERE ((Now()-[UserTable]![LastActivateDate]>" & strDayCount & "));"

Debug.Print strSQL

With CurrentDb
    .Execute strSQL, dbFailOnError
    MsgBox .RecordsAffected & " Records Deleted.", vbOKOnly, "Delete Records"
End With
End Sub

Answer 2

Whilst AccessVandal's solution should work, it is usually considered bad practice to delete data as it may need to be referenced again in the future.

I would recommend that you add a boolean (Yes/No) field and call it Archived.

Then use an update query instead of the delete query and set the Archived field true after 730 days.

Answer 3

Whilst AccessVandal's solution should work, it is usually considered bad practice to delete data as it may need to be referenced again in the future.

Hi isladogs,

It depends ...

Sometimes you HAVE to delete (interesting?) data in accordance to your Privacy Statement.

But also check your related data.

A little more efficient would be to calculate first the reference date, and then delete all records older then the reference date.

Imb.

Answer 4

Agreed...but that should be the exception. I did say USUALLY considered bad practice.

In the past I sometimes used to move very old data to an archive database e.g. data for students and staff who had left that school. We could then reference that data e.g. to write references many years later if needed. 

However maintaining separate databases does make for significantly more work. 

Answer 5

Thank you for the code, very strange that I did not receive email alert about new replies. I ussually receive email notification when I post other threads on other forums (such as Visual Studio forum, same on msdn.microsoft.com)

Three questions:

1. Why use strDayCount? Why not Dim DayCount As Integer, then Set DayCount = 730 ? I don't think I need InputBox, but I don't understand why use String for counting day difference.
2. Why DELETE UserTable.LastActivateDate? Why not Delete * ? I do mean deleting records (UserID and LastActivateDate) if the user has not activated for about two years.
3. From your WHERE statement, assuming it is not LastActivateDate field, it is just RandomDate field (it can be a future date), today is 9/25/2020, will ((Now()-[UserTable]![LastActivateDate]>" & strDayCount  delete only records with RandomDate BEFORE than 9/25/2018 ? Will it also delete records with RandomDate AFTER 9/25/2022?

Thanks.

DELETE UserTable.LastActivateDate

Answer 6

Thank you for the concern, but it is non-important data(otherwise I would not put it Access file), not a big deal to DELETE it. Need to keep file size small, 

Answer 7

You might like to take a look at DeleteDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo file illustrates a number of means for deleting (with or without confirmation), marking for deletion, or restoring records marked for deletion.  The option to Delete Multiple Records does so by date.  For the purposes of the demo, on the basis of the 'last in, first out' principle, it defaults to deleting all rows with a date in the last year by means of the following code in the form's Load event procedure:

    Me.txtDateFrom = DateAdd("yyyy", -1, VBA.Date())

In your case you'd change this to:

    Me.txtDateFrom = DateAdd("yyyy", -2, VBA.Date())

and amend the SQL DELETE statement to the equivalent of:

    strSQL = "DELETE * FROM Employees WHERE DateAppointed <= #" & _
        Format(Me.txtDateFrom, "yyyy-mm-dd") & "#"

---

Ken Sheridan, Stafford, England

Answer 8

Hi VA_er,

1. You can set the variable to an Integer if you want to. However, the InputBox requires a String/Text variable.

If the count is permanently set to 730, just ignore the InputBox and remove the variable. Set it in the "strSQL".

2. You could try with all fields "*". Flavors of SQL commands (MSSQL, Oracle, IBM..etc) may work with all "*" or without it. I'm just using the Syntax which MS Access can recognized.

3. The Now() is the built-in Function of Access. It is the current date and time return to your SQL string. It is not Random. If you want to indicate "From-Date and To-Date". Change it to "WHERE [UserTable].[LastActivateDate] Between #9/25/2018# and #9/25/2022#"

Access does not have "Today" function. It is "Now", which will set to Today's Date/Time.

If you prefer, you can try Ken's demo.

HTH.