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?
See Question&Answers more detail:osI 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?
See Question&Answers more detail:osA 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.