Querying and Null

  • Thread starter Thread starter alex
  • Start date Start date
A

alex

I'm using Access '03.

I have a table in Access with eight columns. Some of the rows contain
null values. When I create a simple query of this table (without any
parameters) it of course returns all the records including the null
values. My question lies when I modify the query to return specific
results. In the design view, I tell the query to return values in a
particular column [Not Like "*A*" And Not Like "*B*"]. This
query works and returns all values not like A and B; however, it fails
to return the null values. Why is it that the query omits any null
values when they're not referenced in any criteria clause?

Thanks for any help.
 
Null essentially means "unknown". Therefore, when you query for equality or
inequality, fields with Null in them are ignored, since it's not known
whether or not they should be selected.

You need to explicity check IS NULL or IS NOT NULL to retrieve rows with
fields that are Null.
 
NULL values propagate through many expressions and cause the whole
expression to be null. To see what I mean, type the follwing statements
into th Debug window & see what you get:

debug.print null like "a*"
debug.print not ( null like "a*" )

To check a field for values like "*A*", treating NULL as a *match* (not
a fail):

nz([fieldname],"A") like "*A*"

HTH,
TC (MVP Access)
http://tc2.atspace.com
 
Nulls are a little hard to wrap one's mind around. Think of Null as meaning
"Don't Know". This is different than 0 or and empty string.

Say that I'm creating a database of people's physical attributes and one is
"eye color." I don't know your eye color, but I'm almost 100% positive that
you have eyes. Therefore I leave that column null. Now when I ask "Blue Or
Green" I shouldn't return any results as I just don't know you eye color!

Therefore if you want to see Nulls, you need to add a "Or Is Null" in the
criteria.

P.S. Don't ever ask me what time it is unless you want to know how to build
a clock. ;-)
 
Back
Top