Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Query not retrieving null fields

Discussion in 'Information Technology' started by Isa, Nov 17, 2005.

  1. Isa

    Isa
    Expand Collapse
    Guest

    Hi,

    I have a simple query in access.
    Here is the SQL:
    SELECT Table1.[Field]
    FROM Table1
    WHERE ((Not (Table1.[Field])="Y"));

    In the field column, there are only rows of Y and null fields.
    I want to select only the null fields, but when choosing the criteria not
    "Y", it does not return any rows.
    The datatype is Text and field size is 1.
    I have tried using a different field size and it still doesn't work.
    If I choose to select Null fields then the query works.

    Any idea why it doesn't work if I choose not Y in the criteria (therefore
    wanting to list null fields by default).

    Hope this makes sense.

    Thanks for your help.
     
  2. Loading...

    Similar Threads Forum Date
    Query about Jhatka Meat by Shooting in Head Sikh Sikhi Sikhism Aug 26, 2011
    Who is a sikh? A non sikh friend's query!! Sikh Sikhi Sikhism Apr 30, 2010
    General Query Hard Talk Sep 4, 2008
    Power of pauri's in Japji Sahib query Sikh Sikhi Sikhism Aug 17, 2006
    Sikhism a query Book Reviews & Editorials Aug 2, 2005

  3. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    Your expression will return rows where the result of the expression is True.
    But the result of a comparison with Null is Null. Null represents an unknown
    value. Is an unknown value equal to or not equal to 'Y' (or any other known
    or unknown value)? The answer is unknown, i.e. Null.

    To select only rows with Null values in the field, change the criteria to
    ....

    WHERE Table1.Field Is Null

    --
    Brendan Reynolds

    "Isa" <Isa@discussions.microsoft.com> wrote in message
    news:06185F2F-5454-46DE-9F11-7D3C31FD7B84@microsoft.com...
    > Hi,
    >
    > I have a simple query in access.
    > Here is the SQL:
    > SELECT Table1.[Field]
    > FROM Table1
    > WHERE ((Not (Table1.[Field])="Y"));
    >
    > In the field column, there are only rows of Y and null fields.
    > I want to select only the null fields, but when choosing the criteria not
    > "Y", it does not return any rows.
    > The datatype is Text and field size is 1.
    > I have tried using a different field size and it still doesn't work.
    > If I choose to select Null fields then the query works.
    >
    > Any idea why it doesn't work if I choose not Y in the criteria (therefore
    > wanting to list null fields by default).
    >
    > Hope this makes sense.
    >
    > Thanks for your help.
     
  4. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Try the WHERE clause of:
    WHERE (Table1.[Field] Is Null) OR (Table1.[Field] <> "Y");

    For an explanation, see the first part of this article:
    The Query Lost My Records! (Nulls)
    at:
    http://allenbrowne.com/casu-02.html

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Isa" <Isa@discussions.microsoft.com> wrote in message
    news:06185F2F-5454-46DE-9F11-7D3C31FD7B84@microsoft.com...
    >
    > I have a simple query in access.
    > Here is the SQL:
    > SELECT Table1.[Field]
    > FROM Table1
    > WHERE ((Not (Table1.[Field])="Y"));
    >
    > In the field column, there are only rows of Y and null fields.
    > I want to select only the null fields, but when choosing the criteria not
    > "Y", it does not return any rows.
    > The datatype is Text and field size is 1.
    > I have tried using a different field size and it still doesn't work.
    > If I choose to select Null fields then the query works.
    >
    > Any idea why it doesn't work if I choose not Y in the criteria (therefore
    > wanting to list null fields by default).
    >
    > Hope this makes sense.
    >
    > Thanks for your help.
     

Share This Page