Query Returning too many records

[bobdydd]

Hi

Ms Access 2007
I have a table called ztblProduct with 2850 items in it.

1. When I run the following query it returns 2850 items
SELECT ztblProduct.Category, ztblProduct.SupplierProductTitle
FROM ztblProduct;

2. When I run the following query it returns 13 items. Which is correct because there are 13 Clocks in ztblProduct
SELECT ztblProduct.Category, ztblProduct.SupplierProductTitle
FROM ztblProduct
WHERE (((ztblProduct.Category)="CLOCKS"));


3. But when I run the following query it returns 2195 items. THIS IS WRONG! It should return 2850-13 = 2837 items
SELECT ztblProduct.Category, ztblProduct.SupplierProductTitle
FROM ztblProduct
WHERE (((ztblProduct.Category)<>"CLOCKS"));

I have tried a restart, a rebuild, I have also tried the NOT LIKE Operator but it still returns 2195 items

Can anyone help?
BTW it's my birthday today

Thanks

 

[Gloops]

bobdydd wrote, on 16th June 2013 13:51 UTC + 2 :

Hi

Ms Access 2007
I have a table called ztblProduct with 2850 items in it.

1. When I run the following query it returns 2850 items
SELECT ztblProduct.Category, ztblProduct.SupplierProductTitle
FROM ztblProduct;

2. When I run the following query it returns 13 items. Which is correct because there are 13 Clocks in ztblProduct
SELECT ztblProduct.Category, ztblProduct.SupplierProductTitle
FROM ztblProduct
WHERE (((ztblProduct.Category)="CLOCKS"));


3. But when I run the following query it returns 2195 items. THIS IS WRONG! It should return 2850-13 = 2837 items
SELECT ztblProduct.Category, ztblProduct.SupplierProductTitle
FROM ztblProduct
WHERE (((ztblProduct.Category)<>"CLOCKS"));

I have tried a restart, a rebuild, I have also tried the NOT LIKE Operator but it still returns 2195 items

Can anyone help?
BTW it's my birthday today

Thanks

Hello,

What do you get with this ?

SELECT COUNT(*) FROM ztblProduct
WHERE Category = Null

--

 

[bobdydd]

Thanks for answering Gloops

It was returning all the Nulls along with the <>"CLOCKS"
Feeling a bit sheepish.

Thanks anyway

 

[Gloops]

bobdydd wrote, on 16th June 2013 15:42 UTC + 2 :

Thanks for answering Gloops

It was returning all the Nulls along with the <>"CLOCKS"
Feeling a bit sheepish.

Thanks anyway

You mean you found the source of the problem ?

 

[John W. Vinson]

Thanks for answering Gloops

It was returning all the Nulls along with the <>"CLOCKS"
Feeling a bit sheepish.

Thanks anyway

Just for the lurkers (you know this already I gather):

NULL is a funny beast. It means "this value is unknown, unspecified,
uninitialized". As such, nothing is equal to NULL (Gloop's expression
[Category] = Null will return NO records); but nothing is UNequal to NULL
either! A criterion of <> "Clocks" will not return either TRUE or FALSE for a
record where Category is NULL; it will return NULL - which will cause no
record to be retrieved.

A criterion of

<> "Clocks" OR IS NULL

will get the desired records.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com