sql query wrong result

[kkshansid]

select count(*) 
from result 
where  reslt<>"A"

why my access query counts null within "A" category
and resultant doesnt include null data. m very confused.
Kindly help me how to correct it.
thanks in advance
ACCESS 2007
WINDOW 7

[jimatqsi]

Your question is not very clear. Best advice I can offer for now is to always wrap potential Nulls in NZ to avoid guessing what will happen with Nulls altogether.

Select count(*) from result where nz(reslt,"A")<>"A"

will avoid counting Nulls.

Select count(*) from result where nz(reslt,"")<>"A"

will include Nulls in the count

Jim

[Rabbit]

You can't compare values to nulls because nulls have no values to compare. You must either specifically account for nulls using something like is null or you need to convert null values to something else using something like Nz.

[kkshansid]

null is also not equal to "A" then why its wrong?

[zmbd]

kkshansid

perhaps these two articles will help you understand:

> What is a Null value in Access? Access 2002

In Access, a Null value indicates missing data in a field. A field could contain a Null value because the information is not known, or because the field doesn't apply to the record. A Null value is not the same as a value of 0 (zero) or a zero-length string ("") because those values are defined - you know what they are.

Because a Null value is not defined, you do not have enough information about a Null value to compare it to any other value. For example, when you use expressions to specify criteria for a field in a query or an advanced filter, the results will not include Null values in that field. The following example demonstrates this and illustrates the difference between a Null value and a zero-length string:(...)

(I know, V2002; however, the information hasn't changed thru to 2010)
> Examples of expressions that check for null values (Access 2003)
(once again, the information hasn't changed thru to 2010)

[Rabbit]

It's wrong to say null is not equal to "A".

Basically, you don't know that a null value is not equal to A. A null value is undefined, null could equal A, or it could equal B, or C, or D, etc. Null can be equal to anything which is why it has special functions to handle it.

Think of nulls as a container with something inside. You just don't know what's inside. If you said you wanted all the containers without a teddy bear inside, then you wouldn't get the container that has an unknown object inside because it could be a teddy bear.