Any chance one of you guys could take a look:
The module:
Code: Select all
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
The two fields I am using as startdate and enddate are:
[InProgressDate]
[ECD]
In the query I have:
Working days: Workdays([InProgressDate],[ECD])
I'm getting function not defined in the VBE.
Many Thanks