"Working Days" in a query

[SarahO]

I am trying to get an Expression in a query to only count "Working Days". The expression is based on two date fields and is:
([ActualCompleteDate]-[ComponentArrivalDate]) and I want the answer to include only days from Monday to Friday that are actual "Working days" i.e. not bank holidays etc. I have no idea how to do "working days" in Access.
E.g.
29/12/05 - 22/12/05 would = 2 if it only counted work days, however I am getting the answer of 7 since it counts the weekend and Xmas.

 

[reclusivemonkey]

Holidays are dependent on which country you are working in. Access can do "Mon-Fri" but for anything else you will need to track your own holiday dates. I think there was a post about this the other day, if you search the forum you may find a ready made solution.

 

[Bat17]

have a look at this thread
http://www.access-programmers.co.uk/forums/showthread.php?t=87474

Peter

 

[SarahO]

Thanks BAT17 & Reclusive Monkey you pointed me in the right direction. I obtained almost what I wanted - see below in italics

I have used the SQL below to calculate the working days including the "DateApproved" and "DateReceived" and exclude weekends and holidays. However, how do you modify this script so it will work out the difference between the "DateApproved" and "DateReceived" excluding the holidays and weekends?
E.g.
If the Date Received = 23/12/2006
and the Date Approved = 29/12/2006
24 & 25/12/2006 = Weekend
26 & 27/12/2006 = Holidays, in the tblHolidays table
The above script returns "3" in the NoOfWorkingDays
I don't want it to include the "DateReceived" so it will return "2" in the NoOfWorkingDays.


Originally Posted by Jon K
To test for null dates, you can use the IIF() function with IsNull():-

IIF(IsNull([Date Approved]) or IsNull([Date Received]), Null, [Date Approved] - [Date Received] - (DateDiff("ww", [Date Received], [Date Approved], 7) - (Weekday([Date Received]) = 7)) - (DateDiff("ww", [Date Received], [Date Approved], 1) - (Weekday([Date Received]) = 1)) - DCount("*","[tblHolidays]","[HolidayDate] between DateValue('" & [Date Received] & "') and DateValue('" & [Date Approved] & "')")) + 1




Any help with this would be gratefully received.
Thanks
Sarah