Weekend/Holiday Exclusion:

[DHook]

Hello -

I am trying to write a query in which Weekends and Holidays are excluded. I understand that Access 2007 does not have the ability to exclude w/e, holidays; but I have a found a VBA function (shown below); however, when I run the Compile:debug I get an error in the string (nWeekdays = Weekdays(startDate, endDate))


The below stems from the site; http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx.
I've created the Holiday table in the string as well but cant seem to get past the error.

Any help is appreciated. Thank you.

Public Function Workdays(ByRef startDate As Date, _
ByRef endDate As Date, _
Optional ByRef strHolidays As String = "Holidays" _
) As Integer
' Returns the number of workdays between startDate
' and endDate inclusive. Workdays excludes weekends and
' holidays. Optionally, pass this function the name of a table
' or query as the third argument. If you don't the default
' is "Holidays".
On Error GoTo Workdays_Error
Dim nWeekdays As Integer
Dim nHolidays As Integer
Dim strWhere As String

' DateValue returns the date part only.
startDate = DateValue(startDate)
endDate = DateValue(endDate)

nWeekdays = Weekdays(startDate, endDate)
If nWeekdays = -1 Then
Workdays = -1
GoTo Workdays_Exit
End If

strWhere = "[Holiday] >= #" & startDate _
& "# AND [Holiday] <= #" & endDate & "#"

' Count the number of holidays.
nHolidays = DCount(Expr:="[Holiday]", _
Domain:=strHolidays, _
Criteria:=strWhere)

Workdays = nWeekdays - nHolidays

Workdays_Exit:
Exit Function

Workdays_Error:
Workdays = -1
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Workdays"
Resume Workdays_Exit

End Function

[pbaldy]

Did you copy in the second function in that link? That line is calling it.

[DHook]

Ok, so I've included the Weekdays script in the Module; and it now compiles:debugs, but when I run the actual query in access I get an error... Undefined Function.

[pbaldy]

The module doesn't have the same name, does it? Can you post the db here?

[DHook]

Thanks for the help; I cannot post the db due to Hipaa data; I actually got the DateDiff results w/ exclusion of w/e and Holidays figured out, but I am now in need of the calculation of future dates with w/e and holidays excluded; Exp: (ReceivedDate+1)... I know, sorry.

[orange]

You may find the iso_Workday... function here to be of value.
The dialog in the text is good too.