Delete by Date In Access

Question

I have a field that contains a date in it, and what i need to do is to delete dates that are 2 years old, so if a date in a field is 6/16/2014 in will delete it, 6/16/2013 will keep it. Is it possible?

Answer 1

A SQL query like this should do the trick, deleting anything 6/17/2013 or after (which I think is what you were looking for).

DELETE FROM MyTableName WHERE MyDateField >= #6/17/2013#

I find it's safer to use >= #6/17/2013# rather than > #6/16/2013#, since often specifying a date without a time means that day at midnight, so anything that happened after midnight on 6/16 is still considered "after" and gets deleted.

Also, you might want to make a copy of your .mdb file before you go doing mass deletes, just in case. :-)

For further reading, check out Office.com: Access SQL: basic concepts, vocabulary, and syntax

---

EDIT: To automatically use today's date minus 2 years, the query would become

DELETE FROM MyTableName WHERE MyDateField > DATEADD("yyyy", -2, DATE())

Course, that might also delete events 6/16/2013 and after, rather than 6/17/2013 and after. If that's a problem, you could tweak it with either nested DATEADD statements, one to subtract 2 years and one to add a day.

FMI: Access DATEADD() Function