Specifying a criteria when the field can be null

[Guest]

I need to include a criterion in a query that is TRUE if a date field is
between two dates. But the date field can be NULL in which case the
criterion should be FALSE.

If I use the expression in the criteria cell: >=dateA AND <(dateB+1)

(where dateA and dateB are text fields in a parameter form, but just writen
here as dateA and dateB for simplicity)

This seems to do what I want, but it seems sloppy because the field can be
null. Is this an acceptable thing to do?

If not, I was considering the use of an IIF statement with IsNull:

IIF(IsNull(dtmField), FALSE, (dtmField>=dateA AND dtmField<=dateB))

I understand that regardless of whether IsNull(dtmField) is true or false,
both the true result and false result of the IIF statement will be evaluated.
If that is true, I think I would get an error if dtmField is null, but I am
using it in the expression with dateA and dateB.

What is the best thing to do?

Thanks,

John

 

[Guest]

John:

Logical expressions involving NULL will evaluate neither to TRUE nor FALSE,
but to NULL. This is because NULL is not a value but the absence of a value
an 'unknown', from which it follows that the result of comparing any thing
with an 'unknown' must also be 'unknown'. i.e NULL. Even NULL = NULL
evaluates to NULL.

The WHERE clause of a query returns rows where the clause evaluates to TRUE,
so it doesn't matter if it evaluates to FALSE or to NULL as in either case
those rows will be rejected. Of course the corollary of this is that if you
are testing for an expression returning NOT TRUE, e.g. WHERE NOT City =
"London" then any rows with NULL at the City column position would not be
returned, which might or might not be what's wanted. If you wanted the rows
returned you'd also have to test for NULL; . WHERE NOT City = "London" OR
City IS NULL;

So an expression MyDate >= [Start date:] AND MyDate < [End date:]+1 will
evaluate to NULL
if MyDate is Null and the row would not be returned. Which is exactly
what's wanted, and not sloppy at all, merely the correct implementation of
Boolean logic.

Incidentally when you do want to test for NULL in a query use IS NULL rather
than the VBA IsNull function. One situation where you might want to do this
is when you want parameters to be optional. Say in your case you want to be
able to supply both, neither or either one of the two date range parameters
you can test each for NULL in parenthesised Boolean OR operations:

WHERE (MyDate >= [Start date:] OR [Start date:] IS NULL)
AND (MyDate < [End date:]+1 OR [End date:] IS NULL)

This would mean you can return rows within the range, anytime after the
start date, anytime before the end date or anytime at all depending on which
parameters are supplied with values.

BTW when using date/time parameters in a query its prudent to declare them
as otherwise a date in short date format might be interpreted as an
arithmetical expression:

PARAMETERS [Start date:] DATETIME, [End date:] DATETIME;
SELECT *
FROM MyTable
WHERE (MyDate >= [Start date:] OR [Start date:] IS NULL)
AND (MyDate < [End date:]+1 OR [End date:] IS NULL);

This applies whether the parameters are simple prompts as above or
references to controls on a form of course.

Finally, the requirement for both the If True and If False expressions in an
IIf function call to be able to evaluate legitimately does not apply in
queries as it does in code.

Ken Sheridan
Stafford, England

 

[Marshall Barton]

I need to include a criterion in a query that is TRUE if a date field is
between two dates. But the date field can be NULL in which case the
criterion should be FALSE.

If I use the expression in the criteria cell: >=dateA AND <(dateB+1)

(where dateA and dateB are text fields in a parameter form, but just writen
here as dateA and dateB for simplicity)

This seems to do what I want, but it seems sloppy because the field can be
null. Is this an acceptable thing to do?

If not, I was considering the use of an IIF statement with IsNull:

IIF(IsNull(dtmField), FALSE, (dtmField>=dateA AND dtmField<=dateB))

I understand that regardless of whether IsNull(dtmField) is true or false,
both the true result and false result of the IIF statement will be evaluated.
If that is true, I think I would get an error if dtmField is null, but I am
using it in the expression with dateA and dateB.

If you want to exclude Null from the range, your criteria is
fine the way it is. This works without error because Null
is never equal to, less than nor greater than anything
(including another Null). It would be a waste of time to
explicitly check for Null.

Note that there are slight differences between the version
of a function in VBA and what might (or might not) look like
the same function in a query. To check for Null in a query,
it is more efficient to use Is Null instead of calling the
VBA function IsNull(). Also, what you say about IIf
evaluating both parts only applies to the VBA version of
IIf. The query Iff only evaluates the part it returns.

 

[Guest]

Ken and Marsh,

Thank you both very much for excellent answers and additional insights.

John