Delete records using dates 2

[nissan240zx]

Hello All,
A problem with Access.
I have a table with some records. This table gets updated every day (via a via batch job).

Now two of the fields in the table (table name - LODDODD) are as follows:
Field name | Type
printed | Yes/No
LetterDate | Date/Time

Now what I want do is..I want to create a query that will move (Insert to a new table TEMP_REC and Delete from LODDODD) records from the table when the LetterDate is older than 3 days and printed is marked as Yes.
Basically the query should check for any records 3 days older than current date.

For example if I have records with dates 7/16,7/17,7/18,7/19,7/20, the query should not delete 7/18,7/19,7/20 records but should remove records from 7/16,7/17 to TEMP_REC and Delete from LODDODD.

Can this be done. Please help.

Nissan 240 ZX...best Nissan Ever

 

[jrbarnett]

If the table TEMP_REC already exists, you can copy the data across with:

Insert into temp_rec (Letterdate, Printed) 
SELECT LODDODD.letterdate, LODDODD.Printed
FROM LODDODD
WHERE DateAdd ("d", 3, letterdate) < Date();

If the table doesn't exist, you can adapt this as a make table query to create the table at the same time.

Afterwards, you can then execute a second query to delete the records from the original table:

Delete from loddodd where DateAdd ("d", 3, letterdate) < Date()

These can be executed together as part of the same procedure to do both.

John

 

[PHV]

WHERE LetterDate <= Date()-3 AND printed = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886