If Monday Then

[AC5FF]

I run a daily trouble report but I am not getting weekend troubles looked at because the report only looks at today's issues - Query uses =DATE() as a criteria.

I want to run a 3 day report if today is Monday. I have read through several threads here, and other places, but have gotten nothing to work. I would prefer to keep away from VB (I'm terrible there).

If I run the query now, I get 8 results - that's what I expect. If I run the query with >date()-3 I get 26 results. Again, what I would expect.

I've tried modifying the =date() to:
IIf((Format(Date(),"ddd"))="mon",>Date()-3,Date())
and
IIf(Weekday(Date())=2,>Date()-3,Date())

but both of these criteria return zero records. I would have thought either one would work.

Can anyone spot what I may have wrong here?

 

[plog]

I would use a combination of a new calculated field and then a date based criterion to accomplish this. My caclulated field would move Saturday and Sunday to Monday like so:

ReportDate: If (Saturday OR Sunday Then 2 Else Current Day Number)

Then in the criteria area, simply use Weekday(Date())

 

[AC5FF]

plog

I think I understand were you are going. I tried adding this to my query:
reportday: IIf(Weekday([reportdate])=1,2,Weekday(Date()))
Just running this to test making Sunday a Monday.

Two problems I see. 1st, there is a problem in my line above. Everything is getting a reportday of 2 - does not matter if it is Sunday, Tues, Fri, etc...

2nd problem I see... This will set every weekend day to a Monday, and if I am just using a criteria of weekday(date()) then I will get every Sat/Sun/Mon in the table.

Also, using this criteria, running on a Monday would pull every Monday's data from the table, not just todays. At least that's what I see happening.... I think. LOL

 

[plog]

1st problem: You wouldn't use the Date() function in ReportDay at all. You should put [reportdate] in there--sorry to confuse. This would be reportday:

reportday: IIf(Weekday([reportdate])=1 OR Weekday([reportdate])=7,2,Weekday([reportdate]))



2nd problem: You are right, it would pull every Monday. To fix this, add one more criterion. Bring [reportdate] into your query and in its criteria put:

>=(Date() - 2)

 

[AC5FF]

Worked like a champ! Thank You