Query Returning too many records

Discussion in 'Access General' started by bobdydd, Jun 16, 2013.

  1. bobdydd

    bobdydd Guest

    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
     
    bobdydd, Jun 16, 2013
    #1
    1. Advertisements

  2. bobdydd

    Gloops Guest

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

    What do you get with this ?

    SELECT COUNT(*) FROM ztblProduct
    WHERE Category = Null

    --
     
    Gloops, Jun 16, 2013
    #2
    1. Advertisements

  3. bobdydd

    bobdydd Guest

    Thanks for answering Gloops

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

    Thanks anyway
     
    bobdydd, Jun 16, 2013
    #3
  4. bobdydd

    Gloops Guest

    bobdydd wrote, on 16th June 2013 15:42 UTC + 2 :
    You mean you found the source of the problem ?
     
    Gloops, Jun 16, 2013
    #4
  5. 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
     
    John W. Vinson, Jun 17, 2013
    #5
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.