Either my files are behaving badly, or I just came across something that probably has caused me a lot of mistaken queries in the past. Maybe I should've known.

Given table TblA, with the following fields:

ID: "1", "2", "3"... (text field, no duplicates)
Group: "0", "", "1" (text field, and the "" is a true null)
Value: 54.8, 64.3, 255.3... (double field)

select records with query QryChoose, given by the sql string:

SELECT TblA.ID,TblA.Value, TblA.Group
FROM TblA
WHERE (Not (TblA.Group)="1");

I have discovered that this "not equal to" criteria in the WHERE clause doesn't just omit the records with a value of 1 in the Group field, but also omits all the records with a null in that field. What have I done wrong?

Hi,

I think you just need to better understand what is NULL. Basically, null means "unknown." It doesn't mean "empty."

Since it is unknown, you cannot compare it to anything. That means you cannot use the comparison (=) operator.

To check for null values in queries, you'll have to use "Is Null" or "Is Not Null."

So, if you want your query to return all records that are not in Group 1, including nulls, you might try something like:

SELECT ID, Value, Group
FROM TblA
WHERE Group <> 1 OR Group Is Null

Note: "Value" and "Group" are probably reserved words in Access. You might consider changing the names of your fields to avoid additional problems in the future.

Hope that helps...

Thanks. This does make sense now. Still, over 5 years of access use, I never realized. Of course nulls are often bad practice, so I'll go ahead and tack this one on as additional evidence.

Neglected that the field names were reserved. Was just using them for demo/posting purposes.




Edited by: st3powers on Wed Dec 2 23:20:48 EST 2009.

You're welcome. Glad to hear that you weren't using reserved words. Good luck with your project.

Hi
If I might just throw in briefly..

>> nulls are often bad practice, so I'll go ahead and tack this one on as additional evidence

I wouldn't worry about that too much.
Yes - it can be argued (not by me) that no field should ever be Null. That the ultimate goal of a relational design has records not created when no value is present.
Personally - life's waay too short for that. Normalisation is the ideaology to obey, but you have to draw the line.

Nulls are a part of database life - and it's no secret that I love 'em!
They're predictable and even advantageous at times - once you understand the concept.
You've taken a step forward in their understanding here thanks to theDBguy. For a bigger step consider an even bigger picture.

Tables with mountains of Null fields may well be improperly normalized.
Fear that - but not Null itself. :-)

Cheers.