Delete record if it is over a year old (1 Viewer)

[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.

 

[Pat Hartman]

I'm with plog on this. Although, sometimes we actually want to archive data. This is never done by "touching" a record manually. You decide what your archive schedule is and run a query daily/weekly/monthly/annually - whatever - that copies the records to be archived to the archive database and then once that is OK, you can run a delete query to remove them from the active database. Remember - every day, new records become "old" so if you want your cutoff that close, you need to run the "archive" process in the background EVERY time the db opens as the first step. Otherwise, pick a period. You'll need a log table if you use this method so you can log the date when the archive ran so you only run it weekly/monthly/ etc.

Leaving the old data in the actual table means that you have to change all your existing queries to ignore the old data. OR you need to make the date range optional in at least the edit form because that is the form where you are most likely to want to look at old data.

 

[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.