Handling null date values in Access queries

Question

I have a table containing null values as dates, 10 in all. In a query based on this table, I want to compare a date in the record with a reference date which I am doing fine with Access VBA. Any of the fields in the record containing dates on the table could be NULL. My VBA code runs fine when the NULL values are located at either the beginning of a record or is the last field in a record.

The query errors I am getting are when the NULL date fields occur in the middle of a record. For example two non - null dates, followed by a null date, followed by a final non - null date field.

I am comparing dates, hence I don't think I can use the NZ() function to force a non null value.

Is there some other methods I can use to correctly handle these NULL values? Appreciate the help from the community!

Answer 1

You can use Nz, for example:

DiffDays = DateDiff("d", YourDateVariable, Nz([YourFieldValue], YourDateVariable))

will return 0 in any case where YourFieldValue should be Null.