Reputation: 1195

Need to Allow Null or "" values for Dates - Access Query Data type mismatch

Basically I'm trying to use a Module or Public functions to pull a datediff that is only for business days. Everything works as far as the code is concerned but for some reason with a particular date field (one I've added after the database has been in production for some time) the code is not working correctly and I'm getting the "Data Type Mismatch in Expression". I'm 99% sure this is a data problem. If I compare two different dates it runs, I've created a test table with 10 records and it runs.

The field is set to Date/Time. I guess my question is, is there anyway to get rid of the ""'s or make it so the code will accept these blanks as nulls? or convert them?

This is where I call the function in the query:

Exp1: BusinessDays([IntCallDate],[aIntCall1])

And here is the code in the module...

Thanks for any help - MUCH appreciated!!!

Public Function BusinessDays(dteStartDate As Date, dteEndDate As Date) As Long
On Error GoTo err_workingDays
    Dim lngYear As Long
    Dim lngEYear As Long
    Dim dteStart As Date, dteEnd As Date
    Dim dteCurr As Date
    Dim lngDay As Long
    Dim lngDiff As Long
    Dim lngACount As Long
    Dim dteLoop As Variant
    Dim blnHol As Boolean
    Dim dteHoliday() As Date
    Dim lngCount As Long, lngTotal As Long
    Dim lngThanks As Long
    If IsDate(dteStartDate) And IsDate(dteEndDate) Then 'added here begin
    dteStart = dteStartDate
    dteEnd = dteEndDate

    lngYear = DatePart("yyyy", dteStart)
    lngEYear = DatePart("yyyy", dteEnd)

    If lngYear <> lngEYear Then
        lngDiff = (((lngEYear - lngYear) + 1) * 7) - 1
        ReDim dteHoliday(lngDiff)
    Else
        ReDim dteHoliday(6)
    End If

    lngACount = -1

    For lngCount = lngYear To lngEYear
        lngACount = lngACount + 1
        'July Fourth
        dteHoliday(lngACount) = DateSerial(lngCount, 7, 4)

        lngACount = lngACount + 1
        'Christmas
        dteHoliday(lngACount) = DateSerial(lngCount, 12, 25)

        lngACount = lngACount + 1
        'New Years
        dteHoliday(lngACount) = DateSerial(lngCount, 1, 1)

        lngACount = lngACount + 1
        'Thanksgiving - 4th Thursday of November
        lngDay = 1
        lngThanks = 0
        Do
            If Weekday(DateSerial(lngCount, 11, lngDay)) = 5 Then
                lngThanks = lngThanks + 1
            End If
            lngDay = lngDay + 1
        Loop Until lngThanks = 4

        dteHoliday(lngACount) = DateSerial(lngCount, 11, lngDay)

        lngACount = lngACount + 1
        'Memorial Day - Last Monday of May
        lngDay = 31
        Do
            If Weekday(DateSerial(lngCount, 5, lngDay)) = 2 Then
                dteHoliday(lngACount) = DateSerial(lngCount, 5, lngDay)
            Else
                lngDay = lngDay - 1
            End If
        Loop Until dteHoliday(lngACount) >= DateSerial(lngCount, 5, 1)

        lngACount = lngACount + 1
        'Labor Day - First Monday of Septemeber
        lngDay = 1
        Do
            If Weekday(DateSerial(lngCount, 9, lngDay)) = 2 Then
                dteHoliday(lngACount) = DateSerial(lngCount, 9, lngDay)
            Else
                lngDay = lngDay + 1
            End If
        Loop Until dteHoliday(lngACount) >= DateSerial(lngCount, 9, 1)
        'MsgBox dteHoliday(5)

        lngACount = lngACount + 1
       'Easter
        lngDay = (((255 - 11 * (lngCount Mod 19)) - 21) Mod 30) + 21

        dteHoliday(lngACount) = DateSerial(lngCount, 3, 1) + lngDay + _
                (lngDay > 48) + 6 - ((lngCount + lngCount \ 4 + _
                lngDay + (lngDay > 48) + 1) Mod 7)
    Next


     For lngCount = 1 To DateDiff("d", dteStart, dteEnd)
        dteCurr = (dteStart + lngCount)
        If (Weekday(dteCurr) <> 1) And (Weekday(dteCurr) <> 7) Then
            blnHol = False
            For dteLoop = 0 To UBound(dteHoliday)
            'MsgBox dteHoliday(dteLoop) & "  " & dteLoop
                If (dteHoliday(dteLoop) = dteCurr) Then
                 blnHol = True
                End If
            Next dteLoop
            If blnHol = False Then
                lngTotal = lngTotal + 1
                'MsgBox dteCurr
            End If
        End If
    Next lngCount

BusinessDays = lngTotal
Else                'Add
BusinessDays = -1 ' add
End If   'add


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



End Function

Upvotes: 1

Answers (4)

phoog

Reputation: 43056

You're getting a data type mismatch because you have declared the parameters as Date type. While a Date/Time column in the database can hold a null value, a Date variable in VBA cannot. You must therefore declare the parameters as Variants, and do some type checking at the head of your function.

This means that my comment to another answer (saying that IsDate will always return true here) is misleading. Rather than deleting the meaningless IsDate check, you should make the check meaningful by changing the parameter type from Date to Variant.

Hope this helps.

Upvotes: 0

RolandTumble

Reputation: 4703

I'm not sure that this line:

If IsDate(dteStartDate) And IsDate(dteEndDate) Then 'added here begin

is necessary, since you'll get Type Mismatch errors if you try to feed other types of values into the function. In any case, you also/instead should have something like:

If dteStartDate <= dteEndDate Then

with the Else portion returning a "known bad" answer, the way your code does here:

Else                'Add            
    BusinessDays = -1 ' add            
End If   'add

This is just an expansion of the answers already posted by Jim Anderson and mwolfe02 . If you accept this answer/vote it up, you should also vote up theirs....

Upvotes: 0