Problem with previous VBA Script: WorkingDays

[TechyWannaB]

I am using the provided VBA script and it works great as is (with no holiday table). However, I do need to account for holidays, so I created a Holiday table.
However, when I run the query, I get a 'tblHolidays' does not exist. So, I renamed that part of the code to 'Holiday' (to match the table name).
Now, when I run the query, I get the following error:
"Error 2001:
Description: You canceled the previous operation."

Any assitance would be appreciated.
Thank you.





jaaret (Instructor)
16 Aug 06 19:42
Does Access have a function similar to Excel's NetWorkDays function? I would like to have a calculated field that displays the number of work days between now and a due date.

Thanks,
Jaaret
Check Out Our Whitepaper Library. Click Here.
Helpful Member!RuralGuy (TechnicalUser)
16 Aug 06 23:11
The answer to your question is no so try this:
Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
'-- Return the number of WorkingDays between StartDate and EndDate
On Error GoTo err_workingDays

Dim intCount As Integer

If IsDate(StartDate) And IsDate(EndDate) Then
If EndDate >= StartDate Then
'-- Force Incoming Dates into consistant format
StartDate = CDate(Format(StartDate, "Short Date"))
EndDate = CDate(Format(EndDate, "Short Date"))

intCount = 0
Do While StartDate < EndDate
StartDate = StartDate + 1
If Weekday(StartDate, vbMonday) <= 5 Then
'-- Use the following code if you have a "Holiday" table
' If Weekday(StartDate, vbMonday) <= 5 And IsNull(DLookup("[Holiday]", "tblHolidays", "[HolDate] = #" & StartDate & "#")) Then
intCount = intCount + 1
End If
Loop
WorkingDays = intCount
Else
WorkingDays = -1 '-- To show an error
End If
Else
WorkingDays = -1 '-- To show an error
End If

exit_workingDays:
Exit Function

err_workingDays:
MsgBox "Error No: " & Err.Number & vbCr & _
"Description: " & Err.Description
Resume exit_workingDays

End Function

 

[PHV]

Which line of code highlighted when in debug mode at the time the error raises ?
I guess the following:
If Weekday(StartDate, vbMonday) <= 5 And IsNull(DLookup("[Holiday]", "tblHolidays", "[HolDate] = #" & StartDate & "#")) Then

So, you must have a table named tblHolidays with at least 2 fields named Holiday and HolDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886