Query results not showing nulls

[JJBaseball]

Hello,

I have written a query against a MS Access table that looks something like:

Select * from myTable when color <> "blue"

It correctly does not return any rows with "blue" in the color column. But
it also does not return rows with Null in the column.

Can someone tell me why and how to fix it?

Thank you

 

[ionic-fire via AccessMonster.com]

<UnTested Air Code>

SELECT * FROM myTable WHERE (color <> 'blue' OR color Is Null)

</UnTested Air Code>

You must specifically tell it to include records that have a Null value in
that field. I like to include parenthesis and a boolean OR statement so that
either part being true will cause the record to be returned in the result set.

 

[Duane Hookom]

Null is an unknown so you can't compare it to Blue. You can convert the nulls
to a string like:
Select * from myTable where color & "" <> "blue";
or use
Select * from myTable where color <> "blue" Or color is Null;

 

[Marshall Barton]

I have written a query against a MS Access table that looks something like:

Select * from myTable when color <> "blue"

It correctly does not return any rows with "blue" in the color column. But
it also does not return rows with Null in the column.

You should think of Null as Unknown. Since the value is
unknown it could be blue or anything else, it can not be
said it is not blue. A simpler thought is that Null is
never equal (or not equal) to anything, not even another
Null

You can get the records with null in the query result sveral
ways. A simple one is to use a criteria like:
<> "Blue" OR Is Null