Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    34

    Unanswered: Query for multiple blank fields in a table

    Hi to all,
    I have a table that each record has about 8 fields for test values. I would like to query the table to see if any tests are not done (blank fields, or null fields) and display the people (each record) that are not done yet. I know that by putting "Is Null" in the Criteria for a query field will do that, but only for one field. How do you set it up to check all fields and return the entire record if at least one is blank? Thanks for any advice in solving this problem.

    David

  2. #2
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    If you are creating this in the query builder of access then where it is asking for the criteria out "Is Null" then on the next field you want as "Is Null" put it on the next line down, you will notice that the is an "Or" criteria so it will show these records if there is Null fields in them as well as the other Null field.

    Hope i explained well there, if not post back and ill try explain better
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

  3. #3
    Join Date
    Jul 2004
    Posts
    34
    For the most part, I understood your post. However, I seem to have a problem getting it to work when there are more than just two fields. Since the actual thing can sometimes supply more than words, I've attached an example (very simplified) of what I'm working with. It contains one table that has the following fields:

    Name
    Test1
    Test2
    Test3
    Test4

    I added four different names (users) with test data. Some fields were left blank. I made a lookup query that at the moment has no criteria since I'm not sure how to set this up. The query will pull up all four entries (as it should). If set up properly, it should pull up only three (since one person has all filled in. The only fields that I'm checking for blanks in are all but the Name field.

    Again, to just restate what I'm trying to accomplish, I want to create a query that will pull up records (or users in this case) that have one or more incomplete tests (noted by a blank field in the Test columns). I'm almost certain that this is simple, but I haven't been able to find the answer. Again, all help is appreciated. Thanks!

    David
    Attached Files Attached Files

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    add a field to the query:

    IIF(len(field1)=0, 0, 1) + IIF(len(field2)=0, 0, 1) + IIF(len(field3)=0, 0, 1) + IIF(len(field4) = 0, 0, 1)
    and the criterion is
    <4

    len(nz(fieldX, "")=0, 0, 1) might help if the field is allowed NULL

    i don't claim that it will be fast!

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    ... And I like to do this:

    ... WHERE (( SomeField & '' ='') OR (SomeField1 & ''='') OR (SomeField2 & ''="") OR(SomeField4 & ''='') ...
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I also prefer the method M Owen uses because you only need one test to see if the field is Null or an Empty String.

Posting Permissions

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