Results 1 to 8 of 8

Thread: Blanks (XP)

  1. #1
    New Lounger
    Join Date
    Jun 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Blanks (XP)

    Have a file in Access, which has 19,000 records. Each records has 15 fields. How do I design a query to show all the records that have Blanks fields.
    I know about criteria but there is not enough levels, HELP!

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Blanks (XP)

    You can overcome this by going into SQL view and putting in the criteria manually.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Blanks (XP)

    You can select some rows in the query grid and select Insert | Rows from the menu. This will give you extra "levels".

    You can also write the SQL for the query directly. To write SQL, select View | SQL. For a table tblData with fields Field1 to Field15, the SQL would look like this:

    SELECT * FROM tblData WHERE Field1 Is Null Or Field2 Is Null Or Field3 Is Null Or Field4 Is Null Or Field5 Is Null Or Field6 Is Null Or Field7 Is Null Or Field8 Is Null Or Field9 Is Null Or Field10 Is Null Or Field11 Is Null Or Field12 Is Null Or Field13 Is Null Or Field14 Is Null Or Field15 Is Null

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Blanks (XP)

    Actually you can do it in the query design grid as well - you just need to add criteria rows. To do that in the grid, place you cursor in a cell, and choose the Insert / Rows command until you have enough rows.
    Wendell

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Blanks (XP)

    The things you learn.

    I must remember that one, good one Wendell.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Blanks (XP)

    In this case, couldn't you do the same thing with a calculated expression concatenating the fields to be tested using the "+" operator? If any of the fields were null, the entire expression would evaluate to null and you could simply use Is Null as the criteria value.
    Charlotte

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Blanks (XP)

    I like it! Much more elegant than 15 OR statements that result from the grid or from typing in SQL. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>
    Wendell

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Blanks (XP)

    Clever. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    You can use any number of fields to test this.

Posting Permissions

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