Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 469
  • Last Modified:

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
0
keschuster
Asked:
keschuster
  • 4
  • 2
1 Solution
 
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
 
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
 
keschusterAuthor Commented:
gotcha - thanks

I hate nulls
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now