null's in a WHERE

  • Thread starter Thread starter ;-\)
  • Start date Start date
;

;-\)

I have a query like
SELECT * FROM tbl WHERE A='x' AND B='y' AND C<>'printed'

C is either null, "printed", or some error message.

It works fine as long as C has a non-null value.

I know I am missing something.

I have also tried

AND (C=Null OR C<>'printed')



I can fix my code to always put something in C, but I would like to
understand the problem.
 
Try:
AND (C Is Null OR C<>'printed')

Two nulls are never considered equal.

If you think of Null as meaning "unknown", that makes sense: it's like
asking,
Does Unknown = Unknown?
the answer is:
Don't know.

More information:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html
 
OK, I read some posts and got it to work

SELECT * FROM tbl WHERE A='x' AND B='y' AND (C IS NULL OR C<> 'printed') ;

SO why is not C<>'printed' good enough?
 
James

Let's assume there are three records, with values for C of Null, "printed",
and "error 9999". If you are a computer and are told "WHERE C<>'printed'",
which ones will you pick? Be very very literal.
 
Allen Browne said:
More information:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Click to expand...

I'm a medical informatics guy, not an Access guru. I'd gradually
figured out I was having trouble with NULL, and like most novices
thought that when I write a "not equals" type query, the NULL rows
should be returned along with non-NULLS that met the not equal
criteria.

I finally resolved to post about this, and dutifully navigated to
microsoft.public.access.queries. I never got to post, because a quick
Google look showed I was not alone.

Which is finally to say, be sure to read Allen Browne's page on NULLs,
and, by extension, everything else he writes. Fabulous work Allen,
thank you for your pages and your response here.

john
(e-mail address removed)

meta: jfaughnan, jgfaughnan, Access, Microft Access, queries, SQL,
NULL value, not equal, rows returned
 
Excellent.
All feedback appreciated.

John Faughnan said:
I'm a medical informatics guy, not an Access guru. I'd gradually
figured out I was having trouble with NULL, and like most novices
thought that when I write a "not equals" type query, the NULL rows
should be returned along with non-NULLS that met the not equal
criteria.

I finally resolved to post about this, and dutifully navigated to
microsoft.public.access.queries. I never got to post, because a quick
Google look showed I was not alone.

Which is finally to say, be sure to read Allen Browne's page on NULLs,
and, by extension, everything else he writes. Fabulous work Allen,
thank you for your pages and your response here.

john
(e-mail address removed)
Click to expand...
 
Check the article.

It explains why:
Null <> 'printed'
and
Null = 'printed'
can never be true.
 
James

I re-read you posts and wish to point out your first comment:
It works fine as long as C has a non-null value.
Click to expand...

and your most recent:
That set should include {Null, error 99999}

BUT that is not the way it works.
Click to expand...

Perhaps I jumped to a conclusion. How 'bout if you tell me what it DOES do,
and what you WANT it to do...

More info, please...

Jeff Boyce
<Access MVP>
 
Back
Top