Results 1 to 8 of 8

Exclude form inputs in an AND query if blank

  1. #1
    hkcaps is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2019
    Posts
    4

    Exclude form inputs in an AND query if blank


    I have a query set up as a simple AND query that uses 5 different fields for its criteria in a form for the user to input. If one or more of the criteria fields is left blank, I want to exclude that criteria as part of the AND search and just run the query off the criteria fields that has input. Is this possible?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,593
    One way, though it will look goofy in design view after saving.

    http://theaccessweb.com/queries/qry0001.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    hkcaps is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2019
    Posts
    4
    Thanks for the reply! The difference is if the field is left blank, I don't want all records. I just want the query to run the AND filter only for those fields where input was entered.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,593
    I probably won't say this right, but the "all records" is just in relation to that field. It should work as you wish when combined. Give it a try, using that method for each criteria.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    hkcaps is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2019
    Posts
    4
    I got rid of 3 of the criteria fields so that I could test it out on 2 fields and it seems to work! Thanks for the advice!

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,593
    Happy to help and welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    hkcaps is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2019
    Posts
    4
    Thanks for the welcome! Actually, I had one more question related to this. If I wanted to "clear" the query results so that no records show, is there a way to do that? I tried to leave the all the criteria fields blank and run the query and it got hung-up. I assume because it is trying to pull all records for all the fields.

  8. #8
    Join Date
    Apr 2017
    Posts
    983
    Quote Originally Posted by hkcaps View Post
    If I wanted to "clear" the query results so that no records show, is there a way to do that?
    Run the query like
    Code:
    SELECT * FROM YourTable Where SomeField = ImpossibleValue

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 05-10-2018, 07:43 AM
  2. Query of Multiple User Inputs
    By Radtastic10 in forum Queries
    Replies: 3
    Last Post: 05-18-2016, 04:23 PM
  3. Take Query Inputs From a Table
    By toonarmy in forum Queries
    Replies: 1
    Last Post: 11-13-2012, 09:01 PM
  4. Replies: 18
    Last Post: 10-10-2012, 10:10 AM
  5. selecting Sub-Form data from two inputs
    By techexpressinc in forum Queries
    Replies: 19
    Last Post: 12-03-2010, 11:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums