Can't query DateDiff

[AceRoadRunner]

I am using the function below to give me the difference between two dates
excluding weekends and holidays and the formula works, but anytime I try to
query the data it gives me a "Data type mismatch in criteria expression"
error.

I just want to query all records that are less than or equal to 5 days, but
I get this error. I have also tried just doing a simple filter from the table
and I get this error. I'm sure there is an issue with the function below, but
I can't figure it out.

Any help with this would be greatly appreciated.


Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, vbSaturday) + _
DateDiff("ww", dtmStart, dtmEnd, vbSunday)) + 1
'Fix bug found where months starting on Saturday or Sunday always over state
by one day
If Weekday(dtmStart, vbMonday) > 5 And Day(dtmStart) = 1 Then
CalcWorkDays = CalcWorkDays - 1
End If

'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tbl Holidays", "[holiday date]
between #" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

 

[Danny J. Lesandrini]

If you're calling this from a query, are you, perhaps, passing a NULL
value for either of the dates? You might try passing Nz(StartDate, Date())
and Nz(EndDate, Date()) instead, or just exclude rows that have a null
in either of these fields.

 

[AceRoadRunner]

That worked. Thank you so much. The query i was looking at had 3000+ records
and I didn't realize that is was producing an error on the records that
didn't have a resolved date. Your suggestions worked. Now I just have to
figure out why the query runs slow from with-in the form.

Thanks again.

 

[John W. Vinson]

Now I just have to
figure out why the query runs slow from with-in the form.

Because it's got to call a custom function (which calls a couple of builtin
functions) for *EVERY RECORD* in the recordset. It's bound to be slow!