NetworkDays in Access?

sburgess · March 15th, 2006, 10:59 AM

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!

Code:

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

kindler · March 15th, 2006, 02:01 PM

You need to add a table of holidays. From there you can do a select query on the table with dates within the range you're looking at, convert the query into a recordset, and subtract the size of the recordset from the number of days you returned with the DateDiffW function.

sburgess · March 15th, 2006, 02:13 PM

Thanks for the information. I will try it.

I'm still new to programming. on the

Code:

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

is there a way to tell it not to give an error on a "null" value? I'm running a query, and using the DateDiffW in an expression, to give me a total of dates, minus "onhold", "offhold" dates, but there are times that there are no values in the "onhold", "offhold" fields, and when I run it, it is giving me an error on this line.

kindler · March 15th, 2006, 04:28 PM

IIf(IsNull(value),0,value)
or
IIf(IsNull(value),'0',value)
or
IIf(IsNull(value),'',value)

One of those three should work depending on the data type required.. I think.

sburgess · March 16th, 2006, 10:30 AM

I'm sorry to be a pain about this, but I guess I'm not getting this.

Here is the expression line that I have in my 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:

Code:

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

I can understand a little what you're explaining, but I'm getting lost when I'm trying to write it into my code.

I started under the NumWeeks code line with:

Code:

IIf(IsNull(BegDate),' ',

but then I get stuck, telling it what to do from that point. Any help would be great!! :D