archive table entries

[zerobug3000]

I need to archive the contents of a table after it is 3 years old, and then once 5 years old to delete the entries. I read another tread and seen i need to use the append query and delete query but i need to make it so that the end user can adjust the time scales easily.

 

[Jack Cowley]

You need to create parameter queries. You do this by having an unbound field(s) on a form, having the user fill in the data and the query getting the data from the form. A simple example would be two unbound fields name BeginDate and EndDate on a form named Form1. In the query you would put code like this in the criteria line of the date field:

Between [Forms]![Form1]![BeginDate] And [Forms]![Form1]![EndDate]

The user would enter the dates and run the query and it would select the dates based on the users entry.

hth,
Jack

 

[zerobug3000]

i really cant work out how to do that at all. The unbound fields, make a new table with them two fields on ?? then base a form on them, enter the dates? Then i made a query for the table and put the criteria in. it asks then to enter a begin date and end date and still doesnt give me the results i would of expected. However my task is to move records from on table to an archive table once there 3 years old from the date of entry. then to delete them from the archive table after 5 years.

 

[Jack Cowley]

I'm sorry, but there are so many ways to do this and I really don't have the time to spell out all of the details for you. I tried to find a simple explanation of what I am suggesting but I was unable to do so. All I can say is that you have unbound control(s) on an unbound form and you refer to that form and the control(s) on that form with code like this in the criteria line of the append and delete queries:

[Forms]![FormName]![ControlName]

Now when you put data into the unbound control(s) on the form and click the buttons the queries will get the criteria information from the form. If your delete query is set up to delete all records where the year is specified in the criteria then putting 2001 into the control on the form and clicking the delete query button all records for 2001 would be deleted.

I hope this helps....

Jack