Not equal criteria in query

[mmogul]

I am running a simple query that excludes a value in a particular column. This is a text column that can contain null values.

For example, the table might have two columns: a1 and a2 and have data like:

a1 a2
a x
a x
a x
b x
b x
b x
c x
c x
c x
s
sd
ss

When I run a query and set the criteria for field a1 as:
Not "c"

The query excludes records that are not equal to "c" but also excludes all records that are equal to null. I would expect the records where field a1 is null to be displayed.

Can someone explain why this occurs?

Thanks.


Thanks.

 

[randy700]

It would be easier to evaluate possible errors if you were to show your query SQL.

Randy

 

[PHV]

Consider NULL as unknown and then you'll understand that NULL is NOT 'C'
You may try this (SQL code)
SELECT *
FROM yourTable
WHERE Nz(a1,"?")<>"C"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeAtWork]

WHERE a2 <> "C" OR a2 IS NULL

 

[dhookom]

I typically use a variation of PH's solution:

SELECT *
FROM yourTable
WHERE a1 & "" <> "C"

Duane
Hook'D on Access
MS Access MVP

 

[traingamer]

I would expect the records where field a1 is null to be displayed.

That is incorrect. If you want to include them use the solution provided by JoeAtWork.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[dhookom]

I think the answer from JoeAtWork is the only one that will not work since it looks at field a2 rather than a1. Otherwise all of the options should work.

Duane
Hook'D on Access
MS Access MVP

 

[JoeAtWork]

it looks at field a2 rather than a1

Yes I picked the wrong field but it is the syntax that is important.
Reasons I prefer my solution:
1. I expect "pure SQL" to be more efficient than the NZ function
2. I don't trust that concatenating a Null to an empty string will always be supported in the future
3. My syntax works also for SQL Server and Oracle - useful if the OP ever upgrades to one of those platforms
4. I find it the easiest to read, it is the most explicit as to what is actually being checked for

 

[mmogul]

thanks all.

 

[dhookom]

Good points JoeAtWork. If there are other criteria in the where clause you only have to remember to put ()s around the ORs:

WHERE (a1 <> "C" OR a1 IS NULL) AND ...

Duane
Hook'D on Access
MS Access MVP