Query results not showing nulls

  • Thread starter Thread starter JJBaseball
  • Start date Start date
J

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
 
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;
 
JJBaseball said:
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.
Click to expand...


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
 
Back
Top