Try to incorporate Or into an IIF statement

[MalcyJ]

Hey there all, new here, so go easy...

I am trying to work out this situation.

For a day to day database, show me all records, where if it is Monday today, I want to see Friday, Saturday and Sunday, otherwise I want to see yesterday only.

Trying to write this in Access in the QBE grid.

So far I have come up with:

IIf(WeekdayName(Weekday(Date()),True,1)="Mon",(Dat e()-1 Or Date()-2 Or Date()-3),Date()-1)

Trouble is, if the day is Monday, this returns nothing.

What bugs me is that: ()-1 Or Date()-2 Or Date()-3 by itself does infact return Fri, Sat, Sun.


What am I doing wrong in the IIF statement that if the 3 dates are needed, it doesn't work?

Cheers,

Malc.

[MikeTheBike]

Hey there all, new here, so go easy...

I am trying to work out this situation.

For a day to day database, show me all records, where if it is Monday today, I want to see Friday, Saturday and Sunday, otherwise I want to see yesterday only.

Trying to write this in Access in the QBE grid.

So far I have come up with:

IIf(WeekdayName(Weekday(Date()),True,1)="Mon",(Dat e()-1 Or Date()-2 Or Date()-3),Date()-1)

Trouble is, if the day is Monday, this returns nothing.

What bugs me is that: ()-1 Or Date()-2 Or Date()-3 by itself does infact return Fri, Sat, Sun.


What am I doing wrong in the IIF statement that if the 3 dates are needed, it doesn't work?

Cheers,

Malc.

Hi

You could try this

SELECT Table1.ThisDate
FROM Table1
WHERE Weekday([ThisDate])=Weekday(Date())-1 Or Weekday([ThisDate])=IIf(Weekday(Date())=2,Weekday(Date())-2,Weekday(Date()-1)) Or Weekday([ThisDate])=IIf(Weekday(Date())=2,Weekday(Date())-3,Weekday(Date()-1))
ORDER BY Table1.ThisDate;

Weekday() is a function that return a number for th day of the week entered.
The default is 1=Sunday, 2 = Monday etc. (this can be changes with an optional argument, try looking at the Access Help.

There are probably better solutions that this but have to stop somewhere !?

MTB

[MikeTheBike]

Hi

You could try this

SELECT Table1.ThisDate
FROM Table1
WHERE Weekday([ThisDate])=Weekday(Date())-1 Or Weekday([ThisDate])=IIf(Weekday(Date())=2,Weekday(Date())-2,Weekday(Date()-1)) Or Weekday([ThisDate])=IIf(Weekday(Date())=2,Weekday(Date())-3,Weekday(Date()-1))
ORDER BY Table1.ThisDate;

Weekday() is a function that return a number for th day of the week entered.
The default is 1=Sunday, 2 = Monday etc. (this can be changes with an optional argument, try looking at the Access Help.

There are probably better solutions that this but have to stop somewhere !?

MTB

Hi

Having reflected overnight I decided there was a flaw in the las query suggesed, I think this will do what is required ?

SELECT ThisDate
FROM Table1
WHERE ThisDate=Date()-1 Or ThisDate=IIf(Weekday(Date())=2,Date()-2,Date()-1) Or ThisDate=IIf(Weekday(Date())=2,Date()-3,Date()-1)
ORDER BY ThisDate;
 

??

MTB