Query DateDiff calculation excluding weekends

[Masterfinn]

Here is what I currently have setup:



Table: myOrders_tbl
Fields:
OrderID
DateOpened
DateClosed

I then have a Form "myOrders_frm" which allows us to choose 2 dates and then it will provide us a count of all the orders that were 'closed' between those dates that were open for 10days or less

In my Form i have the calculated field linked to a Macro with the following criteria:
Dcount("[OrderID]","qryDays")

Then my query "qryDays" has this:
Field DateClosed
Critiera >= firstdate AND <= seconddate


Field DateDiff("d", DateOpened, DateClosed)
Critiera >=10

** i know some of the above syntax is incorrect but i just simplified it

This is all working correctly and it produces a count of all the orders that were closed in 10 days or less from when they were opened. The problem is that if an order was opened one week and then closed the next this qry will calculate the weekends as part of the 10 days.
I woud like to somehow alter this qry to run the datediff but exclude the weekends.
So basicly just count weekdays for the 10 day count.

Is is possible, and if so how would go about doing this?

Thanks!!!

[pbaldy]

Doug has a function here that might work for you:

http://www.pacificdb.com.au/Support/...nfunctions.htm

Other solutions I've seen involve custom functions that account for holidays and weekends.

[Masterfinn]

Thanks for the quick reply. I took a look at that site and was able to setup my Datediff so that it will now calculate only weekdays. I did some testing with it and it appears to be working correctly.

Thanks again,
MF

[pbaldy]

No problem, glad it worked for you.