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