Counting Days Function _ Error 13 Type Mismatch (1 Viewer)

[Johnny Drama]

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

 

[Johnny Drama]

Never mind. I figured it out. Had the wrong field names in the function call. time to go back to bed.

 

[jdraw]

There is a function here that allows for Holidays and non-standard weekends.

When you post and talk about an error, it would be helpful if you showed the line on which the error is reported. I don't see where you have identified DateIssued or DateReceived.

Good luck with your project.

 

[arnelgp]

try calling your function in your query like this:

Work_Days([DateIssued], [DateReceived])

before running your query, make sure to modify your function to this:

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(CDate(BegDate))
EndDate = DateValue(CDate(EndDate))
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, EndDate)
EndDays = 0
Do While DateCnt <= EndDate
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

 

[Minty]

This doesn't make much sense

Function Work_Days([COLOR="green"]BegDate [/COLOR]As Variant, [COLOR="green"]EndDate [/COLOR]As Variant) As Long

Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer


On Error GoTo Err_Work_Days

[COLOR="green"]BegDate [/COLOR]= DateValue([COLOR="Red"]DateIssued[/COLOR])
[COLOR="green"]EndDate [/COLOR]= DateValue([COLOR="red"]DateReceived[/COLOR])

The items in Red are not declared anywhere- so what are they ? Why would you pass variables in (In Green) then overwrite their values ?