sqlms-accessiif

This simple date query does not give me any results if on the date in question

IIf(Weekday(Date()+1)=6,Between Date()+1 And Date()+3,Date()+1)

I need to use this criteria in a query to filter out dates.

At the end of each day this criteria runs to bring up what bookings are to be put in to the system for tomorrow, works fine if it's false but when it's a friday and I need it to bring up any and all for over the weekend it comes back blank.

Again this works fine if I input Between Date()+1 and Date()+3 as the criteria on it's own just not working as part of the iif statement.

Really not sure why this isn't working.

Any help would be appreciate thank you.

I understand that to test it today it needs to be

IIf(Weekday(Date()+1)=3,Between Date()+1 And Date()+3,Date()+1)

This still comes up blank.

Solution

  • Create an expression:

    IsFriday: Weekday(Date())=5

    Then, for True in the Criteria row of this, filter the date field:

    Between Date()+1 And Date()+3

    For False in the Criteria row of this, filter the date field:

    Date()+1