Button to archive data based on date - how?

[scar]

I want to have a button on my form which archives records in a table (copies to another table and deletes original) where the date in the record is > 6 months old.
Is it best to use a macro to run a couple of queries, or shall I do this in VBA SQL? Also, how do I calculate '6 months back from current date'?

Many thanks all!

 

[Gazer44]

you could try putting this into your date field of the query:-

<Date()-180

This will look at everything over 180 days old (which is pretty close)

 

[evalesthy]

You could use either queries or sql depending on what your needs are and how comfortable you are with either method. If you want to archive all records over 6 months old you could just run an append query to copy those records using criteria offered by Gazer44 to the archive table and then run a delete query to remove records from first table. If other criteria are involved then they will have to be added to query criteria as well. Be sure append and delete queries use same criteria or you will have an inconsistent append/delete. Access Help can give you examples of the two types of queries.

 

[scar]

Thanks very much. I've got it working in queries with the 180 method. Thanks also for the advice, evalesthy.

 

[Maquis]

Scar, use VB instead of macros. As evalesthy stated, you can have either pre-defined queries in your database or you can create the SQL in the VB module.

I think having pre-defined queries will be a bit more efficient in the long run, and you can execute them in your module just by using the docmd.openquery statement.

Public Sub archive_click()
docmd.openquery &quot;append query name here&quot;
docmd.openquery &quot;delete query name here&quot;
End Sub
Maq
<insert witty signature here>