Criterion [<>"C"] excludes null values

  • Thread starter Thread starter Mark Hammer
  • Start date Start date
M

Mark Hammer

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.
 
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.
 
Thanks, Brendan. I couldn't ask for a more concise or authoritative answer.

--Mark



Brendan Reynolds said:
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.

--
Brendan Reynolds (MVP)

Mark Hammer said:
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.
Click to expand...
Click to expand...
 
Back
Top