Criterion [<>"C"] excludes null values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark Hammer - 04 May 2005 19:52 GMT

Access XP.

I have a table with a text field, which can have letter values A, B, C, or
D, or it can be null.

A query on that table, with criterion for that field set to [<>"C"] excludes
the records with null values as well as those having "C".

This doesn't make sense to me. In order to get what I want, I need to have
the criterion say [<>"C" OR Is Null].

There must be a principle here that I have not learned. TIA to anyone who
can explain to me why [<>"C"] also excludes nulls.

Mark Hammer
Lake Oswego, Oregon, U.S.

Reply to this Message

Brendan Reynolds - 04 May 2005 20:02 GMT

You're query will return all records for which the expression
[FieldValue]<>"C" evaluates as True. But the expression Null <> "C" does not
evaluate as True. It evaluates as Null.

This is because Null represents an unknown value. Is an unknown value equal
to or not equal to a known value (or, for that matter, another unknown
value)? The answer is neither True nor False, but unknown, i.e. Null.

This is what we mean when we say 'Nulls propagate' - if any part of an
expression evaluates as Null, the entire expression will usually evaluate as
Null.

Signature

Brendan Reynolds (MVP)

> Access XP.
>
[quoted text clipped - 12 lines]
> Mark Hammer
> Lake Oswego, Oregon, U.S.

Reply to this Message

Mark Hammer - 04 May 2005 20:29 GMT

Thanks, Brendan. I couldn't ask for a more concise or authoritative answer.

--Mark

> You're query will return all records for which the expression
> [FieldValue]<>"C" evaluates as True. But the expression Null <> "C" does
[quoted text clipped - 25 lines]
>> Mark Hammer
>> Lake Oswego, Oregon, U.S.

Reply to this Message