Using NetWorkDays in Access?

[sburgess]

Is there a way for Access to recognize holidays in a function? I found the DateDiffW function that counts just weekdays, but I also need it to count holidays as well, within the same function. Here is the coding that I have so far, and it's working well. What I'm using it for is a "on hold" "off hold" calculation, that will be pulled into measurables. Any help would be appreciated!

Public Function DateDiffW(BegDate, EndDate)
    Const SUNDAY = 1
    Const SATURDAY = 7
    Dim NumWeeks As Integer
    
    If BegDate > EndDate Then
        DateDiffW = 0
    Else
        Select Case Weekday(BegDate)
            Case SUNDAY: BegDate = BegDate + 1
            Case SATURDAY: BegDate = BegDate + 2
        End Select
        Select Case Weekday(EndDate)
            Case SUNDAY: EndDate = EndDate - 2
            Case SATURDAY: EndDate = EndDate - 1
        End Select
        NumWeeks = DateDiff("ww", BegDate, EndDate)
        DateDiffW = NumWeeks * 5 + Weekday(EndDate) - Weekday(BegDate)
    End If

End Function

Also, is there a way to tell it if it sees a null value, not to give it an error? I have set this expression set up in a query

Expr2: IIf(IsNull([OnHoldREQ]),DateDiffW([Requisition Rec'd],[Submitted to Manager]),DateDiffW([Requisition rec'd],[Submitted to Manager])-DateDiffW([OnHoldREQ],[OffHoldREQ]))

When I run this, I get an error in my coding, highlighting this line:

NumWeeks = DateDiff("ww", BegDate, EndDate)

Any help would be appreciated!!

 

[Ian Mac]

Have a look at:

http://www.utteraccess.com/forums/access/access268573.html

Should help you.
You need to realise with Access you don't need to optional Holidays Argument as an input. I had trouble with this logic for ages.

 

[sburgess]

Thanks for the information. I have printed off that post, and it looks like it will help. Going back and forth between Excel and Access gets confusing, with what needs an argument and what does not.

Thanks again!