Delete record if it is over a year old

[MaleNurse325]

On a form I have two date fields. I am trying to delete the record on the form if the dates are greater than one year old.
I bet its simple but I am a bear of little brain.

 

[plog]

Generally its a bad idea to actually delete data in a database. Why must it be deleted? How about just not have it show up on the form?

You could build a query that only shows those records that meet the criteria of records you want to see (less than one year old) and then base your form off that query. No need to delete or even mark records as old, the computer would just "automatically" figure it out itself and not show them on that form.

 

[The_Doc_Man]

For the U.S. Navy, I was required to keep records for a specified amount of time (that varied between what I was actually keeping.) Knowing ahead of time that I would have an archiving and purging requirement, I had a flag in each table that would need it. So ... for the most part I used a query to pull records younger than age-limit 1. But once a month I ran a query that marked the flag TRUE for records older than age-limit 2 (which was always older than limit 1). Before my maintenance period, I would make an extract of the flagged records by exporting to a spreadsheet. During the maintenance period, I would delete the marked records. THEN I would run the marking query. The final step was a compact & repair.

I had maybe 20 tables that were operational rather than definitional, so I had that much archiving to do. This became complicated enough that I started running the marking queries sequentially from a subroutine to run each (update/marking) query in any sequence. The archiving sequence was a sequence of DoCmd.TransferSpreadSheet (Export) operations based on the queries that only looked at marked records. The delete sequence followed the archiving sequence as a series of Delete queries.