Query not returning null value

[JJBaseball]

Hello,

I accidently posted this question in Reports and don't know how to move it.
So permit me please to post it here as well....

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

 

[NuBie via AccessMonster.com]

To include null in your criteria:

Select * from myTable where color <> "blue" or color is null

 

[Duane Hookom]

I accidently replied in the reports ng. Here is my reply:

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]

Hello,

I accidently posted this question in Reports and don't know how to move it.
So permit me please to post it here as well....

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.

I trird to explain what's going on in the report's group.

 

[vanderghast]

The criteria color <> 'blue' returns true only if it is KNOWN that the
color is different than blue, that is why if the color is null, then the
color is unknown (maybe it is blue, maybe not), and in that case, the test
returns unknown (Null), and that reject the record (only when the result is
true, the record is kept). As others mentionned, adding an OR IS NULL could
then include the records where color is null, since it is true that the
color is null, and : unknown OR true evaluates to true.


Vanderghast, Access MVP