Prev: 65 Opinion Dsiconsolas.com
Next: Report has 126 pages and take forever to run
From: JJBaseball on 26 Oct 2009 15:25
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
From: ionic-fire via AccessMonster.com on 26 Oct 2009 16:12
<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.






JJBaseball wrote:
>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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200910/1

From: Marshall Barton on 26 Oct 2009 16:18
JJBaseball wrote:
>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

--
Marsh
MVP [MS Access]
From: Duane Hookom on 26 Oct 2009 16:17
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;

--
Duane Hookom
Microsoft Access MVP


"JJBaseball" wrote:

> 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
 | 
Pages: 1
Prev: 65 Opinion Dsiconsolas.com
Next: Report has 126 pages and take forever to run