Strange Query Results when Nulls are involved

[ebrooks54]

I haven't noticed this behaviour before, and it really has me scratching my head...

Using Access 2003

I have a table with 12,104 records in it. A query with no criteria returns 12,104 rows.

A query with Message Type = 'TMSG' returns 1,312 rows.
A query with Message Type <> 'TMSG' returns 6,586 rows.

By inspection, the missing rows all contain NULL in the message type field. It appears that using the <> condition has forced the query to ignore any records where the field has no value.

Adding an OR criteria of Message Type IS NULL adds the missing rows to the results, but this does not appear 'normal' to me.

Am I doing something wrong?

 

[PHV]

Consider a Null value as UNKNOWN and then you'll understand the behaviour.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[traingamer]

Simply stated, a null value is neither equal nor not equal to a value.

Rather than adding the or clause, you could use the NZ function:

...Message Type <> nz('TMSG', '')

or simply

...Message Type <> nz('TMSG')


Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[dhookom]

I prefer:
Nz([Message Type],"") <> "TMSG"
or
[Message Type] & "" <> "TMSG"

I try to always use Nz() with 2 arguments.



Duane
Hook'D on Access
MS Access MVP

 

[traingamer]

I try to always use Nz() with 2 arguments.

Me too; I was just trying to provide some options.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard