data type mismatch error

[geonebula]

I have a calculated field that returns a report due date. It uses a function I found to account for weekends/holidays. The function returns a date type.

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date
   [COLOR=green]' Add the specified number of work days to the
    ' specified date.
    
    ' Modified from code in
    ' "Visual Basic Language Developer's Handbook"
    ' by Ken Getz and Mike Gilbert
    ' Copyright 2000; Sybex, Inc. All rights reserved.
    
    ' In:
    '   lngDays:
    '       Number of work days to add to the start date.
    '   dtmDate:
    '       date on which to start looking.
    '       Use the current date, if none was specified.
    '   adtmDates (Optional):
    '       Array containing holiday dates. Can also be a single
    '       date value, if that's what you want.
    ' Out:
    '   Return Value:
    '       The date of the working day lngDays from the start, taking
    '       into account weekends and holidays.
    ' Example:
    '   dhAddWorkDaysA(10, #2/9/2000#, Array(#2/16/2000#, #2/17/2000#))
    '   returns #2/25/2000#, which is the date 10 work days
    '   after 2/9/2000, if you treat 2/16 and 2/17 as holidays
    '   (just made-up holidays, for example purposes only).
    
    ' Did the caller pass in a date? If not, use
    ' the current date.[/color]
    Dim lngCount As Long
    Dim dtmTemp As Date
    
    If dtmDate = 0 Then
        dtmDate = Date
    End If
    
    dtmTemp = dtmDate
    For lngCount = 1 To lngDays
        dtmTemp = dhNextWorkdayA(dtmTemp, adtmDates)
    Next lngCount
    dhAddWorkDaysA = dtmTemp
End Function

However, now when I query against this calculated field to return all dates that are <=Now()+7 I get an error reading:

Data type mismatch in criterion expression.

If I change the query to return all dates <>#06/30/2011# the results will appear for a few seconds before the error message appears again and all records show up #Name?

I've tried casting both my calculated field and/or my query criteria with CDate()to no avail. What am I missing here?

 

[PWise]

check if there a field with an invalid date
run this

Select *
from tablename
where not isdate(datefieldname)

 

[geonebula]

That query caught a few records that were not dates. So I changed the calculated field to:

 Final Report Due: IIf(IsDate([Date and Time EZEDD Released from Lab]),dhAddWorkDaysA(10,[Date and Time EZEDD Released from Lab]),#1/1/1999#)

Yet still any comparisons run against the calculated field get the same error message. IsDate() verifies the Final Report Due is a date, and I can add and subtract time from it. Can you not call functions on calculated fields and then compare against them?

 

[MajP]

I would modify the function. If the passed value is not a date return 1/1/1999. If you are passing a value from a query I always make it a variant and check for null and valid datatype. It is inevitable that you will pass in a null or bad value.

Public Function dhAddWorkDaysA(lngDays As long, _
dtmDate As variant, _
Optional adtmDates As Variant) As Date
   ' Add the specified number of work days to the
    ' specified date.
    
    ' Modified from code in
    ' "Visual Basic Language Developer's Handbook"
    ' by Ken Getz and Mike Gilbert
    ' Copyright 2000; Sybex, Inc. All rights reserved.
    
    ' In:
    '   lngDays:
    '       Number of work days to add to the start date.
    '   dtmDate:
    '       date on which to start looking.
    '       Use the current date, if none was specified.
    '   adtmDates (Optional):
    '       Array containing holiday dates. Can also be a single
    '       date value, if that's what you want.
    ' Out:
    '   Return Value:
    '       The date of the working day lngDays from the start, taking
    '       into account weekends and holidays.
    ' Example:
    '   dhAddWorkDaysA(10, #2/9/2000#, Array(#2/16/2000#, #2/17/2000#))
    '   returns #2/25/2000#, which is the date 10 work days
    '   after 2/9/2000, if you treat 2/16 and 2/17 as holidays
    '   (just made-up holidays, for example purposes only).
    
    ' If not a date return 1/1/1999
    
    Dim lngCount As Long
    Dim dtmTemp As Date
    
    If not isdate(dtmDate) Then
        dhAddWorkDaysA = #1/1/1999#
    else
        dtmTemp = dtmDate
        For lngCount = 1 To lngDays
          dtmTemp = dhNextWorkdayA(dtmTemp, adtmDates)
        Next lngCount
        dhAddWorkDaysA = dtmTemp
     end if
End Function

you will need to make the first argument of dhNextWordDayA also a variant.