Daily Query report

[n90]

Hey guys, I'm trying to run a report that I need to run every morning for the jobs opened the day before, However I would it to also give me Fridays/Sat/Sundays Open jobs when I run Monday. While running the report Tuesday-Friday it will only give me the day before jobs.

 

[David R]

I've got one that goes the opposite direction (I need Monday after Friday), but it should get you started....

        If Format(now(), "dddd") = "Friday" Then 'shift to Monday, not Saturday!
            Me.txtInspDate = Int(now()) + 3
        Else
            Me.txtInspDate = Int(now()) + 1
        End If

You can also use vbMonday in some cases, but it's somewhat unreliable so I just hardcoded mine.

 

[n90]

I've got one that goes the opposite direction (I need Monday after Friday), but it should get you started....

        If Format(now(), "dddd") = "Friday" Then 'shift to Monday, not Saturday!
            Me.txtInspDate = Int(now()) + 3
        Else
            Me.txtInspDate = Int(now()) + 1
        End If

You can also use vbMonday in some cases, but it's somewhat unreliable so I just hardcoded mine.

I'm not exactly sure how to add this to my Query, cause I just currently have 2 were one is set to filter via Date()-1 and the other Date()-3.

 

[Geotch]

Change one of your query criteria to this:

>IIf(Weekday(Date())=1,Date()-3,Date()-1)

I uploaded a sample.

 

[David R]

That doesn't work in criteria, in my experience. It ALWAYS evaluates to the first condition.
http://www.access-programmers.co.uk/forums/showthread.php?t=35942

I had to set two queries up and switch them via VBA based on the day of the week.