Criteria based on iif statement (Delete Query)

[shart00]

There is a delete query that currently ask the user to input the Start and End dates for dates to be deleted.

In an attempt to update the database a new table DSRDATES has been added that has the fields Monday, Tuesday, etc with the field values being the actual dates (Update weekly)

What is needed is for the delete query to base its record deletion off of the current date or now() and what vaules are in the DSRDATES

Essentially,
iif(now()=[Tuesday]or now()=[Wednesday],[Monday],between [Monday] and now()-2))

Which would ultimately tell the query that if it is ran on Tuesday or Wednesday, delete all records with Monday's date, otherwise delete all records that have a date range between Monday's date and the day before yesterday.
I.E. if ran on Friday, all records with a date range between Monday and Wednesday would be deleted.


Thank you for any and all assistance.

 

[mordja]

shart00,

That is quite a tricky one but this works. Create your delete query as normal. Then in the criteria section of the date field enter


>=IIf(Weekday(Now())=3,Now()-1,IIf(Weekday(Now())=4,Now()-2,Now()-Weekday(Now())+2)) And
<=IIf(Weekday(Now())=3,Now()-1,IIf(Weekday(Now())=4,Now()-2,Now()-2))


This does two checks one for the upper and lower bound of the date range. If it is a tuesday (Weekday(now())=3) then the upper and lower bound will be the same Now()-1 (Monday).

If it is a wednesday Weekday(Now())=4 then the upper and lower bound will be the same Now()-2 (Monday).

Else the lower bound will be Now()-Weekday(Now())+2 (Monday) and the upper bound will be Now() -2.

Enjoy

Mordja

 

[shart00]

Looks great! For testing purposes, I have changed the query to a select query and found one thing. Since today is Friday, the query should be picking up Monday - Wednesday, however it is not picking up Monday. Your help to solve that one item is greatly appreciated.

 

[shart00]

Mordja,
I don't know if it will work right so would be great if you could take a look...

>=IIf(Weekday(Now())=3,Now()-1,IIf(Weekday(Now())=4,Now()-2,Now()-Weekday(Now())+1)) And <=IIf(Weekday(Now())=3,Now()-2,IIf(Weekday(Now())=4,Now()-2,Now()-2))

The only thing I did was change the - weekday(now())+2 to weekday(now())+1

Now Monday's data also appears. Will this mess anything else up in the formula for the different days of the week?

 

[mordja]

shart00,

actually looking at the above you are right. To calculate monday is Now()-Weekday(Now())+1). My mistake. No the rest will work fine.

Mordja

 

[mordja]

Shart00,

I take that back, to calculate monday is
Now()-Weekday(Now())+2)

Now()-Weekday(Now) will give us the last day of last week, +1 sunday, +2 monday. ie for a wednesday the above would pan out as 4 - 4 + 2 = 2 (Monday)
4 - 4 + 1 = 1 (Sunday)

Mordja

 

[shart00]

So what would be the code???

Why if the +2 is used, won't it pick up Monday?

 

[mordja]

Shart00,

I dont know, I think its because now() returns the time as well, so the date comparison doesnt quite work as it should, so maybe Mon is not > Mon 18:30 but Tues is I really dont know. However I worked out that putting DateValue around the appropraite now()s works.

>=IIf(Weekday(Now())=3,DateValue(Now())-1,IIf(Weekday(Now())=4,DateValue(Now())-2,DateValue(Now())-Weekday(Now())+2))
And <=IIf(Weekday(Now())=3,DateValue(Now())-1,IIf(Weekday(Now())=4,DateValue(Now())-2,DateValue(Now())-2))

Enjoy.

Mordja

 

[shart00]

Mordja,
Thank you!!!!

 

[shart00]

Problem in the mist.....
The system has made a few upgrades and now the criteria needs to change and I have tried repeatedly to modify the criteria but is over my head...

If the query is ran on Monday it needs to delete all date within the date range of the previous monday thru sunday I.E. if ran on 11/15/04 it needs to delete 11/8/04 - 11/14/04.
If ran on Tuesday delete previous Monday thru current monday I.E. (11/16/04) it needs to delete 11/8/04 - 11/15/04.
Wednesday= Current Monday only I.E. ran on 11/17/04 delete only 11/15/04
Thursday= Current Monday and Tuesday I.E. ran on 11/18/04 delete 11/15/04 and 11/16/04
Friday = Current Monday thru Wednesday I.E. ran on 11/19/04 delete 11/15/04 - 11/17/04.
Saturday = Current monday thru Thursday I.E. ran on 11/20/04 delete 11/15/04 - 11/18/04.
Sunday = Current Monday thru Friday I.E. ran on 11/21/04 delete 11/15/04 - 11/19/04

Any ideas on how to modify the following code to accomodate????:
>=IIf(Weekday(Now())=3,DateValue(Now())-1,IIf(Weekday(Now())=4,DateValue(Now())-2,DateValue(Now())-Weekday(Now())+2)) And <=IIf(Weekday(Now())=3,DateValue(Now())-1,IIf(Weekday(Now())=4,DateValue(Now())-2,DateValue(Now())-2))

 

[shart00]

Hi,
To throw a twist in things, the code for this is now working, but I am tring to modify this code for a different purpose but am having loads of trouble.
Starting simply, there is a make table query driven by (2) tables (Base (which has the records) and DSRDATES (which is basically a table that holds the dates of week in fields named for the days of the week (i.e. field name = Monday and it holds the date for monday 1/24/05 and so on)

What I am tring to use the code for is so when the query is ran on Monday it will make a table (Reporting) from the table (Base) using records from Base whose date in the field [Date] is between the dates currently in [DSRDATES].[monday] and [DSRDATES].[Sunday]. Then when ran on Tuesday, the query will make a table with records from Base whose [Base].[date] is = to the date in [DSRDATES].[TUESDAY].
Run on Wednesday, the records from Base whose date in [Base].[DATE] is between [DSRDATES].[Monday] and [DSRDATES].[TUESDAY]
Run on Thursday, the records form Base whose date in [Base].[Date] is between [DSRDATES].[Monday] and [DSRDATES].[Wednesday]
and so on...

I have tried to use the following code but it is not working when ran on Monday or Wednesday - Saturday. There seems to be a problem with the "Between".

IIf(Weekday(Now())=2,Between [monday] And [sunday],IIf(Weekday(Now())=3,[monday],IIf(Weekday(Now())=4,Between [monday] And [tuesday],IIf(Weekday(Now())=5,Between [monday] And [wednesday],IIf(Weekday(Now())=6,Between [monday] And [thursday],IIf(Weekday(Now())=7,Between [monday] And [Friday]))))))

When it runs on Tuesday it works just fine and creates a table with data from Base where [Base].[Date]=[DSRDATES].[Monday].

Any suggestions? This has become a real problem.