Query not showing nulls

[Guest]

I created a query which should grab all the records where the field “gen3†<>
“QDâ€. When this query is run, it does not show the records where the field
“gen3†is null. Some of the values are various amounts of spaces and it does
show those records.

I resolved it by asking for where “gen3†<> “QD†or is null. I don’t
recall ever seeing this before.

Shouldn’t the query also grab the null values? A null value does not equal
“QDâ€.

I would appreciate any input.

Thank you,

Rachel

 

[Douglas J. Steele]

By it's nature, Null cannot be used in conjunction with tests for equality
or inequality.

Your work-around is the only way to ensure that you get the Null records
included when you've got a condition for the field.

 

[Norman Yuan]

NULL means "not known" or "no value". So, no comparison can be done on it.
That is, if the value is NULL, you cannot say it is equal to or not equal to
something, it can simply not be compared to. That is why in WHERE clause you
use "gen3 IS NULL", not "gen3=NULL".

So, yes, if you want to include records which "gen3" column value <>'QD' or
"gen3" colum value is null, then you must make the WHERE clause like this

SELECT...WHERE gen3<>'QD' OR gen3 IS NULL

 

[Guest]

Thank you, Doug. I didn't know that.

I really appreciate your quick response.

Rachel

 

[Guest]

Thank you very much Norman.

Rachel

NULL means "not known" or "no value". So, no comparison can be done on it.
That is, if the value is NULL, you cannot say it is equal to or not equal to
something, it can simply not be compared to. That is why in WHERE clause you
use "gen3 IS NULL", not "gen3=NULL".

So, yes, if you want to include records which "gen3" column value <>'QD' or
"gen3" colum value is null, then you must make the WHERE clause like this

SELECT...WHERE gen3<>'QD' OR gen3 IS NULL