#error with null date field using isNull or isDate

[daydog]

I have attempted to write a function in Access 2000 to calculate the
days between two dates. In my Access 2000 the end date can and does
contain nulls. Using Access queries, I have no problem using IS NULL
to evaluate the field, but in my function if isNull is true or isDate
is false, it returns #error. If isNull is false or isDate is true the
function returns what I wanted it to. I could use some help since I am
not smart enough to figure this one out and I haven't found any
articles that directly address my issue. Here is a sample of what is
returned and the code:

FirstDat LastDat BegDat EndDat PatDays
1/1/2006 1/31/2006 1/27/2006 2/3/2006 11:59:00 PM 5
1/1/2006 1/31/2006 1/27/2006 #error


Function PatDays(BegDat As Date, EndDat As Date, FirstDat As Date,
LastDat As Date)

Dim EndDate As Date

EndDate = Format(EndDat, "Short Date")

If IsDate(EndDat) Then

If BegDat > LastDat Or EndDat < FirstDat Then
PatDays = 0
Else

If BegDat < FirstDat Then

If EndDat > LastDat Then
PatDays = (LastDat - FirstDat) + 1
Else
PatDays = (EndDate - FirstDat) + 1
End If

Else

If EndDat > LastDat Then
PatDays = (LastDat - BegDat) + 1
Else
PatDays = (EndDate - BegDat) + 1
End If

End If
End If

Else

If BegDat < FirstDat Then
PatDays = (LastDat - FirstDat) + 1
Else
PatDays = (LastDat - EndDat) + 1
End If

End If

End Function

 

[Douglas J Steele]

GIven that you're declaring BegDate, EndDate, etc. as Dates, you can't pass
Nulls to the function: the only data type that accepts Nulls is Variant.

Try changing your declarations, then check for Nulls inside the function.

Of course, I don't see any evidence that you're using IsNull, as your note
says, so I may be missing something here...

 

[daydog]

That's it. Thank you!