Microsoft Access JetSQL DATE query problem

[chris6976]

Hi

Im trying to create a query for a report in Access 2000.

The basic idea is to select any bookings that have not been confirmed within the previous 5 days of todays date.

ie. select any dates 5 days previous to today's date.

However, the problem is that the company concerned does not operate at weekends, so the 5 days prior to todays date must be "working days".

Does anybody know if this can be achieved, in SQL and how.

 

[kpal29]

Try adding a column to your query like this:
DAY: Weekday([DATE])
This will return a number that corresponds to the days of the week. Sunday = 1, Monday = 2 etc.

Then add another column that uses an iif or case statement to say that depending on the day of week, you must subtract the number of weekend days that fall within the 5 days after the date.