DIFFERING QUERY CRITERIA DEPENDING UPON DAY OF THE WEEK 2

[jimbooth35]

I have an access database that among other data stores within each record the date a transaction on that record occured.
I am trying construct a single query to return records that are over 2 working days old unless the query is run on a monday then the query must return records that are over 3 working days old (sundays don't count as a working day).
I have tried to build an SQL statement using the IF operator but I can't get it to work..
Any ideas ??

 

[SteveR77]

Jim,

If you have a field for the number of working days then you could use something like the following for your criteria:

IIF(DatePart("w",[DateField])=vbMonday,"2", "3")

The IIf above checks the weekday value being equal to 1 which is Monday and if it is the IIf will return a 2 otherwise it will return a 3.

HTH,

Steve

 

[jimbooth35]

Thanks Steve...

However I'm trying to apply the query criteria to the date field itself...

If my criteria is..
<Now()-2
This returns all records over 2 days old

If my criteria is..
<Now()-3
This returns all records over 3 days old

However... what I'm tring to do is combine both criteria in one IIF statement so the query will use one criteria on a monday and the other every other day of the week.
The following...
IIf(DatePart("w",[DateLoaned])="vbMonday",<Now()-2,<Now()-2)
or possibly..
IIf(DatePart("w",[DateLoaned])="vbMonday","<Now()-2","<Now()-2")
Don't work

Is what I'm trying to do possible..??

 

[SteveR77]

Jim,

I do no think that you will be able to apply the IIF logic directly to your field because you will be attempting to analyze and set the field value all at once which will not work.

A couple of work arounds would be to move the logic to a query or to add another field which will have the =IIF logic as it's control source, then you can hide the DateLoaned field if you needed to.

HTH,

Steve

 

[Golom]

SteveR77 gave you the answer
[tt]
< DateAdd ("d",-IIF(DatePart("w", Date())=vbMonday, 3, 2 ),Date() )
[/tt]

 

[jimbooth35]

Golom & SteveR77

Your answer worked perfectly with a minor adjustment to fit my specific needs..

[tt]<Now()-(IIf(Weekday(Now())=2,3,2))[/tt]

Thanks...

Jim