Convert null date to today’s date (XP)

Topic

New Reply

I am trying to convert a missing date in a query into today’s date in a query. I have written the following Function “MissingDate”.
I keep getting #Error when the date is missing. I have included an example database.

Function MissingDate(FieldofMissingDate As Date)

If FieldofMissingDate = Null Then
MissingDate = Now()
Else
MissingDate = FieldofMissingDate
End If

End Function

Reply | Quote

Replies

Instead of this function, use the expression

Nz([FieldName],Now())

If you want to use the function, you must define FieldOfMissingDate as a variant, since a Date variable can’t be Null.

Function MissingDate(FieldofMissingDate As Variant) As Date
If IsNull(FieldofMissingDate) Then
MissingDate = Now()
Else
MissingDate = FieldofMissingDate
End If
End Function

Reply | Quote

Thank you. Now all I have to do is to Format it in Short Date.

Reply | Quote

Unless you specifically needed the time, I’d use Date() instead of Now() in Hans expression.

Reply | Quote

Thank you, that is a good idea.

Reply | Quote