Future deletion of record

[helmi]

In my database system, there is a field that shows the DATE BOUGHT of vehicle.In ten years time from the date bought, i would like Access to automatically delete the vehicle record. My business would want to scrap all vehicles that is ten years old.What must i do? I have no knowlege of visual basic and hope someone can help me out.Thanx....

 

[aflat]

If your table is named "Vehicles" and the field you suggested is named, "DATE BOUGHT".
Your sql query should look like this:

SELECT Vehicles.[DATE BOUGHT], DateDiff("yyyy",[DATE BOUGHT],Date()) AS Over10Years
FROM Vehicles
WHERE (((DateDiff("yyyy",[DATE BOUGHT],Date()))>10));

This uses the DateDiff() function to retrieve the difference in years "yyyy" between [DATE BOUGHT] and Date() or Now() functions (which return the current date or time of the computer you are using. Look up the functions in Access help.

If you make a delete query based on these parameters, and run it once a year in January, or whenever, it will DELETE any [DATE BOUGHT] vehicles older than 10 years.

There are lots of ways to do this but basing a DELETE query on this criteria and then running this query in code from a button or scheduled routine is very simple.

aflat