Johnny Drama
In need of beer...
- Local time
- Yesterday, 22:31
- Joined
- Dec 12, 2008
- Messages
- 211
Good morning all,
I am using the VBA code below to count the number of workdays between two dates. I call the function in a query, but when the query is run I get a Error 13: Type Mismatch. It seems as it may have something to do with the variable declaration, but I'm not sure.
I'm not a VBA coder by an stretch. This is code I found online. Any help would be appreciated. Using Access 2016.
Function Work_Days(BegDate As Variant, EndDate As Variant) As Long
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
On Error GoTo Err_Work_Days
BegDate = DateValue(DateIssued)
EndDate = DateValue(DateReceived)
WholeWeeks = DateDiff("w", DateIssued, DateReceived)
DateCnt = DateAdd("ww", WholeWeeks, DateIssued)
EndDays = 0
Do While DateCnt <= DateReceived
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
Exit Function
Err_Work_Days:
If Err.Number = 94 Then
Work_Days = 0
Exit Function
Else
MsgBox "Error " & Err.Number & ": " & Err.Description
End If
End Function
I am using the VBA code below to count the number of workdays between two dates. I call the function in a query, but when the query is run I get a Error 13: Type Mismatch. It seems as it may have something to do with the variable declaration, but I'm not sure.
I'm not a VBA coder by an stretch. This is code I found online. Any help would be appreciated. Using Access 2016.
Function Work_Days(BegDate As Variant, EndDate As Variant) As Long
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
On Error GoTo Err_Work_Days
BegDate = DateValue(DateIssued)
EndDate = DateValue(DateReceived)
WholeWeeks = DateDiff("w", DateIssued, DateReceived)
DateCnt = DateAdd("ww", WholeWeeks, DateIssued)
EndDays = 0
Do While DateCnt <= DateReceived
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
Exit Function
Err_Work_Days:
If Err.Number = 94 Then
Work_Days = 0
Exit Function
Else
MsgBox "Error " & Err.Number & ": " & Err.Description
End If
End Function