Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: Query question - display only records which have an empty field

    Hi, how do I make an Access Query display only those records where a particular field is empty (has nothing entered into it)?

    Thanks

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    WHERE nz(yourfield, "") = ""
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Aug 2004
    Posts
    364
    Quote Originally Posted by Teddy View Post
    WHERE nz(yourfield, "") = ""
    I created an expression in my query, and added the code, but it returns all records where the field in question does has a value - e.g. "3" and it also returns where there is an empty value e.g. "-1".

    So that code isn't quite doing what I wanted..I just want records displayed where a particular field on the row is empty.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    -1 is not an empty value...

    That filter will not return anything that has a value other than an empty string, or null.

    Could you post a couple sample records that you're querying, the SQL for the query you're currently using, and the results you would like to see.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Aug 2004
    Posts
    364
    Sure I will post later today..

    It is showing "-1" in an empty field...

  6. #6
    Join Date
    Aug 2004
    Posts
    364
    I have attached shots of my table and query.

    The sql for my query is as follows -
    SELECT visit.visitID, visit.visitDate, visit.visitStartTime, visit.visitEndTime, visit.arrived, visit.centreID, tbl_groups.groupname, Nz([beneficiaryID],"")="" AS expr
    FROM tbl_groups
    INNER JOIN visit ON tbl_groups.groupID = visit.groupID
    GROUP BY visit.visitID, visit.visitDate, visit.visitStartTime, visit.visitEndTime, visit.arrived, visit.centreID, tbl_groups.groupname, Nz([beneficiaryID],"")="", tbl_groups.groupID
    HAVING (((tbl_groups.groupID)=[Forms]![tabbed_frm]![lst_link_groups]));
    You can see the table only has two records, one record has a BeneficiaryID entered, the other doesn't, the field is empty.

    I would like the query to only display the record(s) which DO NOT have a value in the BeneficiaryID field.
    Attached Thumbnails Attached Thumbnails query.jpg   table.jpg  
    Last edited by moss2076; 05-06-10 at 19:27. Reason: I said DO when I mean to say DO NOT.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    When limiting a recordset, you need to look at using a WHERE clause if the criteria pertains to each individual row, or a HAVING clause if the criteria pertains to a group of records.

    You would like to see records where BeneficiaryID of each individual record is not null or empty, which is what the nz() function does. You may want to look up nz() in help to see exactly how that works.

    The nz() bit does not belong in either the select or group by clauses...
    Last edited by Teddy; 05-06-10 at 17:29.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Aug 2004
    Posts
    364
    Quote Originally Posted by Teddy View Post
    When limiting a recordset, you need to look at using a WHERE clause if the criteria pertains to each individual row, or a HAVING clause if the criteria pertains to a group of records.

    You would like to see records where BeneficiaryID of each individual record is not null or empty, which is what the nz() function does. You may want to look up nz() in help to see exactly how that works.

    The nz() bit does not belong in either the select or group by clauses...
    Update: I meant to say I would like the query to only display the record(s) which DO NOT have a value in the BeneficiaryID field.

    My mistake in my last post!
    Last edited by moss2076; 05-06-10 at 19:28.

  9. #9
    Join Date
    Aug 2004
    Posts
    364
    I have found the solution - all I need to do is type IS NULL into the criteria section of the field to return records with no beneficiaryID.

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    WOOT!

    Check out the SQL it wrote for you, take note of what it did to the WHERE clause. That will save you tons of headaches in the future!
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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