MS Access- Query with null fields

I really don't understand this and have never seen before.  I want to confirm with you guys/gals I'm not nuts.  I've attached a sample.  Very simple table with a field called criteria.  One of the three records has an X in it.  This query, which in my mind should return 2 records returns 0.


SELECT Table1.test, Table1.criteria
FROM Table1
WHERE (((Table1.criteria)<>"x"));

If I change the query to the follow I can get the two records. (adding NZ)

SELECT Table1.test, nz([criteria]) AS Expr1
FROM Table1
WHERE (((nz([criteria]))<>"x"));


I've never seen this before.  Do you guys see the same result for the first query?  I don't understand how a blank or null field is not equal to X....

thanks
Sample.accdb
keschusterAsked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I just eyeballed your attachment.  

A NULL is not going to <> x, or = x, or = 42, "banana", or #8/22/2012#.
You have to convert it to an actual value, then you can do the comparison.

Nz(criteria, "x") does that.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I want to confirm with you guys/gals I'm not nuts.
There's not enough information here to make an accurate assessment.

A NULL is not equal to anything, especially x.   Most developers in this situation would do a Nz(criteria, "x") if you want NULLs returned, or Nz(criteria, "anything other than x") if you don't want NULLs returned.
0
 
keschusterAuthor Commented:
gotcha - thanks

I hate nulls
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0
 
hnasrCommented:
Confusing NULL!

Think of NULL as a missing, or unknown data value. So if one refuses to tell you how much he has (ie null value), you cannot use his content in your calculation. What do you expect the result to compare an unknown value with 3?

If the in-charge instructs you to consider any one refusing to declare contents as zero, then you can carry on calculation or comparison.  This is NZ function.

So the NZ function is a way to instruct the database to continue calculation and comparison by replacing the unknown content by any agreed value.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
@hnasr:  The question was answered an hour ago there Skip...
0
 
hnasrCommented:
No problem, just consider it as a comment worth adding!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.