1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

MS Access Query not returning blank fields

Discussion in 'Business Applications' started by mariaa33, Jan 28, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    156
    I have a query that has a couple of text fields where in the criteria I put <>"Power". When I put this criteria in, it also does not show me records that the field is blank. If I leave the criteria empty, it shows me all records including the records with the blank fields. How do I get the query to exclude the criteria Power but still show records where that field is blank?
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    How many Tables in the query?
     
  3. Albantar

    Albantar

    Joined:
    Dec 9, 2008
    Messages:
    95
    Try adding something like:

    OR ... = ""
     
  4. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    156
    I have 4 tables and when I try or ="" I get even less records
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Yes you would. You may not be getting the Null Fields due to the Join Settings between the Tables.
    Can you post a Screenshot of the Query in Design View?
     
  6. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Set 2 criteria
    Is Null
    and below it in the Or line
    <>"Power"
    (You can reverse the order, just have them both in.)
     
  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    slurpee, that does the same as Albantar's suggestion, it excludes everything else but Is Null.
     
  8. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    156
    I tried that but it pulls everything even outside my date criteria. I think I am just going to dump it to excel and use a macro to delete the lines that contain Power. Thanks.
     
  9. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    mariaa, you realy should learn how to do this in Access, can you post a Screenshot of the Query in design mode?
     
  10. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    156
    I have attached my design view but it actually has 7 tables 4 are linked to ODBC and 3 regular tables.
     

    Attached Files:

  11. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    OK can you click on the line that Joins the Outage Table to the RootCause Table and tell me what "Option" has been selected please?
     
  12. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    maria, that looks like an 8D Problem Solving Database?
     
  13. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    156
    all outage and only those in root cause where join fields are equal. I don't know what a 8D DB is.
     
  14. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    OK, can I assume then that the Root Cause Table does not have a "Null Value" field?
    From your previous remarks do you want to delete Records that do not have "Power" or No value in the Root Cause Field?
    8D is a Problem Solving Technique.
     
  15. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    156
    Basically I need any field that is not Power but include blank fields. So I should have records that have Card replaced, shelf replaced, <blank>, etc. just not Power.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/795248