Yesterdays Date

[Jez]

When running my query, I want it to pick up only yesterdays visit dates, but
when its a Monday it should show Fridays visits. How is this possible? i dont
want to tell it any dates though. I just want to run my query and show me the
visits.

Thanks, Jez.

 

[Andreas]

To subtract days, you can either subtract a number (if you subtract 1
you get yesterday) or use the DateAdd function.

To test for the day of the week, you can use the Weekday function.

Then use an IIF function to subtract the appropriate number of days
based on the result of the Weekday function.

Regards,
Andreas

 

[tw]

iif(weekday(date()) = 2,date() -3,date()-1)
if today is monday return the date 3 days ago otherwise return yesterday's
day

 

[Afzal]

Here is a complete code example with table structure.

Assume Sunday is 1 day of the week and
Assume Table structre as follows with table name LOGTABLE:
ID autonumber
NAME text
VISITDATE date/time shortdate

Query returns only records for yesterdays records or
if today is Monday, last Fridays records.


SELECT id, Name, IIf(DatePart("w",Date())=2,Date()-3,Date()-1) AS ReviewDate
FROM LogTable
WHERE (((IIf(DatePart("w",Date())=2,Date()-3,Date()-1))=[visitdate]));

Good luck,
Afzal