Custom function parameter & NULL

[wingforward]

I built a custom function that takes a date and determines if 5 months have passed since that date, returning a boolean result. The issue I'm having is that some records have a null value (which I want to return as a false). And those records are preventing the query from running.

My workaround was to add an Nz() around the Date parameter when I feed it into the function, changing the parameter to a Variant rather than a Date. Is that the best way of handling that issue?

Also, I said the records have a null value, but I'm not clear on the difference between Null, Empty, Missing, "", etc. Can someone fill me in?

Public Function NotExpired(Anniversary As Variant)
If Anniversary = 0 Then
NotExpired = False
Else
Dim varMonths As Integer
varMonths = DateDiff("m", Anniversary, Date)
NotExpired = (Not (varMonths >= 5))
End If
End Function

Thanks
DJ

 

[pbaldy]

Personally I would have passed the Null to the function and let it handle it, but your way should work. I have a FAQ on the differences between Null, Empty, etc:

http://www.baldyweb.com/NullEmptyEtc.htm

 

[wingforward]

I tried passing it first, but couldn't get the query to parse, it would give up an error.

How would you parse it?

 

[pbaldy]

I would think the only change needed would be:

If IsNull(Anniversary) Then

or

If Not IsDate(Anniversary) Then

The input parameter would need to be a Variant, as you have it now.