Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: Simple query SQL !stumped!

    I have a 3,900 record query with a field that allows null called SLU2. It is a text field.
    91 records in that field are not null.

    I need the query to only return records with null values of that field so I put Is Null in the criteria (which doesn't look correct when you read SQL).
    Only 922 records come back - it should be 3,809!

    Here's the SQL:
    SELECT qrySLU2.ID, qrySLU2.ACCOUNT, qrySLU2.Date, qrySLU2.STATLU, qrySLU2.STATNOTES, qrySLU2.SLU, qrySLU2.SLU2, qrySLU2.PTLU, qrySLU2.RNOTES, qrySLU2.SalesmanCode, qrySLU2.SalesmanFirst, qrySLU2.SalesmanLast, qrySLU2.SalesmanStatus
    FROM qrySLU2
    GROUP BY qrySLU2.ID, qrySLU2.ACCOUNT, qrySLU2.Date, qrySLU2.STATLU, qrySLU2.STATNOTES, qrySLU2.SLU, qrySLU2.SLU2, qrySLU2.PTLU, qrySLU2.RNOTES, qrySLU2.SalesmanCode, qrySLU2.SalesmanFirst, qrySLU2.SalesmanLast, qrySLU2.SalesmanStatus
    HAVING (((qrySLU2.SLU2) Is Null))
    ORDER BY qrySLU2.ACCOUNT;

    There is no consistency in what records it limited, except it did limit the ones that have a value in SLU2

    Can anyone offer the correct criteria to get the result I need?

    THANKS!

  2. #2
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183
    SELECT qrySLU2.ID, qrySLU2.ACCOUNT, qrySLU2.Date, qrySLU2.STATLU, qrySLU2.STATNOTES, qrySLU2.SLU, qrySLU2.SLU2, qrySLU2.PTLU, qrySLU2.RNOTES, qrySLU2.SalesmanCode, qrySLU2.SalesmanFirst, qrySLU2.SalesmanLast, qrySLU2.SalesmanStatus
    FROM qrySLU2
    GROUP BY qrySLU2.ID, qrySLU2.ACCOUNT, qrySLU2.Date, qrySLU2.STATLU, qrySLU2.STATNOTES, qrySLU2.SLU, qrySLU2.SLU2, qrySLU2.PTLU, qrySLU2.RNOTES, qrySLU2.SalesmanCode, qrySLU2.SalesmanFirst, qrySLU2.SalesmanLast, qrySLU2.SalesmanStatus
    HAVING (((qrySLU2.SLU2) Is Null))
    ORDER BY qrySLU2.ACCOUNT;
    Change the HAVING Clasuse as shown below and try:

    Code:
    HAVING ((len(Trim(NZ(qrySLU2.SLU2," ")))=0))
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  3. #3
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    What's the reason for the GROUP BY clause (which probably explain the "strange" results you get)? You can't expect to retrieve all rows matching a condition while you group them, except if every row is different, which renders the GROUP BY clause useless.

    Normally I would use:
    Code:
    SELECT qrySLU2.ID, 
           qrySLU2.ACCOUNT, 
           qrySLU2.Date, 
           qrySLU2.STATLU, 
           qrySLU2.STATNOTES, 
           qrySLU2.SLU, 
           qrySLU2.SLU2, 
           qrySLU2.PTLU, 
           qrySLU2.RNOTES, 
           qrySLU2.SalesmanCode, 
           qrySLU2.SalesmanFirst, 
           qrySLU2.SalesmanLast, 
           qrySLU2.SalesmanStatus
    FROM   qrySLU2
    WHERE (qrySLU2.SLU2 Is Null);
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •