delete query criteria

[Guest]

I have an append query that appends to a table a bunch of fields. One of
those fields is the date in which I ran the query.

I only want to keep the current days data on the table and the previous days
data on the table (so basically 2 days worth of records). So I thought of
using a delete query that runs before the append runs and using the criteria
to delete all records with a date of <date()-1.

The problem I'm running into is that if the day that I'm running this query
is on a Monday then I need Friday's data (which would be the last business
day that I ran the previous append query) so if my criteria is <date()-1 then
I would end up deleting friday's records.

I know that there is a date functions that I could combine with an iif
statement that basically says that if date() is a Monday then use <=date()-3,
else <date()-1 but it's almost 3am here and my brain is spent.

Any help you can provide would be greatly appreciated.

 

[Douglas J. Steele]

IIf(Weekday(Date()) = 2, DateDiff("d", -3, Date()), DateDiff("d", -1,
Date()))

BTW, constantly inserting and deleting from a table is going to cause bloat
in your database, so that you'll need to compact frequently.

 

[Guest]

Douglas,

Thank you very much.