Results 1 to 14 of 14
  1. #1
    NickWren is offline Advanced Beginner
    Windows XP Access 2013
    Join Date
    Dec 2015
    Location
    Connecticut
    Posts
    70

    Query with more than 4 or 5 criteria as search parameters

    I get the basic concept of a search query with a search form:

    On the form (frmForm1):
    TextBox1(Name): John
    TextBox2(SampleID): 1234
    TextBox3(Status): Health/Unhealth
    TextBox4(Gender): Male/Female
    TextBox5(Age): 42
    TextBox6(Diagnosis): *
    CommandButton1 - Used to update the subform (subformForm1) [Me.subformForm1.Requery]

    Query (qrySearchCriteria):
    Field1: PatientName
    Criteria1: Like "*" & [Forms]![frmForm1]![TextBox1] & "*"



    Field2: SampleID
    Criteria2: Like "*" & [Forms]![frmForm1]![TextBox2] & "*"

    Field3: Status
    Criteria3: Like "*" & [Forms]![frmForm1]![TextBox3] & "*"
    ....

    The problem is once you have to many criterias are used it eventually starts to ignore the last ones or the searches are not properly done. How do I get around this (since its a subform I dont really want to reopen the form (maybe reopening the subform can just refresh the data, but I dont know)).

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Are you putting your all your Criteria on the SAME Criteria line, or on different lines?
    If they are put on the same line, then ALL the conditions must be met (AND conditions).
    If they are put on different lines, then it will return anything that meets any one of the Criteria listed (OR conditions).

  3. #3
    NickWren is offline Advanced Beginner
    Windows XP Access 2013
    Join Date
    Dec 2015
    Location
    Connecticut
    Posts
    70
    I have them on the same line, because I want to make sure they all have priority, however most of the time the fields are blank (the search criteria are completely different so sometimes they are filled out sometimes they are blank).
    How would you recommend I set it up so that all fields can be solo searched or several fields can be searched at once.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Joe's points are correct, and may solve your problem. In this situation, I lean towards dynamic SQL, as shown in the sample db here:

    http://www.baldyweb.com/BuildSQL.htm

    Maybe that's just because I'm comfortable with the code. Or because I'm a control freak.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    While you're pondering the question, maybe be more explicit about the possibilities you really want to include. The question posed does cover
    WHERE "A" AND "B" AND "C"
    vs
    WHERE "A"
    OR "B"
    OR "C"
    but what about the need for
    WHERE "A" AND "B"
    OR "A" AND "C"
    OR "B" AND "C" ?
    The mixing of AND with OR complicates things to the point where a vba solution is often used.

    EDIT: I am fairly quick on the keyboard, yet am beaten to the punch again.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You must have dozed off while typing.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    LIKE and wildcard doesn't always work nicely with number values. In your case, they will work if all ages are 2 digits and all SampleID are 4 digits.

    I NEVER use dynamic parameterized queries and like pbaldy, prefer VBA. Another tutorial http://allenbrowne.com/ser-62.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I NEVER use dynamic parameterized queries and like pbaldy, prefer VBA. Another tutorial http://allenbrowne.com/ser-62.html
    Me too! I don't really like using Form references in my queries. I like building the SQL on-the-fly using the method Paul highlighted.

  9. #9
    NickWren is offline Advanced Beginner
    Windows XP Access 2013
    Join Date
    Dec 2015
    Location
    Connecticut
    Posts
    70
    Okay I will try to see if I can update my self on making the query in SQL, I am more of a beginner (self taught for the last year or so). I post back here if I have any questions about making the query into SQL, thank you for your help so far.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Having said my soapbox piece - your query should work. I am not sure why you feel it has 'too many' criteria and some are ignored. Are any other fields number type or have lookup set in table? Are any Yes/No or date/time types?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    To clarify, I often use dynamic SQL, particularly for search forms with multiple optional criteria, but I do use queries with form parameters. If the query will always be filtered by dates, then I'll typically point it to my criteria form and let it get the date parameters there. I'm lazy; why code it when it's so easy to point it to a form?
    Last edited by pbaldy; 08-14-2017 at 03:50 PM. Reason: fix goof, "do" not "so"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    When it comes to form control references in queries let's not forget that when many of us started cycling there were three wheels. Sometimes four. The implication that there is something inherently wrong with such queries is a bit misleading to absolute novices IMHO.

    As Forrest said, "That's all I have to say about that."

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Not 'inherently wrong', just annoying. Sometimes when developing I want to open query, form, report without being hit with a barrage of parameter input prompts because some object it is dependent on isn't open.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Agree. If telling a novice they can to it this way, I try to add that the form must be open and the relevant controls must contain data. Sometimes I might neglect to do that.

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

Similar Threads

  1. Query ignoring criteria ONLY with parameters
    By Lluewhyn in forum Queries
    Replies: 2
    Last Post: 07-08-2016, 10:34 AM
  2. Help on query criteria for a search
    By urbanhawk in forum Queries
    Replies: 3
    Last Post: 01-31-2014, 04:42 PM
  3. Replies: 1
    Last Post: 07-03-2013, 10:39 AM
  4. Query Parameters for Search Form
    By alsoto in forum Queries
    Replies: 7
    Last Post: 01-25-2012, 01:38 PM
  5. Replies: 9
    Last Post: 05-05-2011, 02:05 PM

Posting Permissions

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