Excluding Weekends in Query

[G. V.]

Hey all,
I am looking for the best way to exclude weekends in a query that uses input from a form for the date span to report. So if the client types in 12/1/04 to 12/6/04 the count of the days in between does not count Sat and Sun. The query I have so far to calculate the days uses DateDiff to calculate the initial days in between, but then I use another IIF calculation to count 12/1/04 to 12/1/04 as one day. See below. I have to be careful on what I add to the query as it can mess up my summary values. This is for a Courier company and they want to be be able to see what has transpired on the current date as well as previous dates minus any weekends.

The DeliverDate field is used to get the date span and in the query this is the criteria:

=[forms]![reportsPrompt]![StartDate] And <=[forms]![reportsPrompt]![EndDate]

This is the dateDiff calc field and it is hidden on the form and gets the number of days as long as the start/end dates are different..
Name:txtDaysDiff
=DateDiff("d",[Forms]![ReportsPrompt]![StartDate],[Forms]![ReportsPrompt]![EndDate])

This is the adusted calc when using the same date for both start and end date.
=IIf([txtDaysDiff]>=0,[txtDaysDiff]+1,[txtDaysDiff])

Hope someone can help. I usually can figure these out, but my brain has been on overload and I can't think straight.

 

[Christian]

Hi GV,

Don't know if this will help you but try to check this discussion thread:

http://tinyurl.com/4sh7u

or use the real link to Google Groups
http://groups-beta.google.com/group..._doneTitle=Back+to+Search&&d#f4e86dfcbd93297f

NumberOfWorkdays:
DateDiff("d", Date1,
Date2)-DateDiff("ww",Date1,Date2,7)-DateDiff("ww",Date1,Date2,1)



Good luck
- Chr

 

[G. V.]

Thanks I'll check it out.