Results 1 to 7 of 7
  1. #1
    guitarzycki is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    20

    OpenForm with multiple search criteria and wildcards

    Please help! I am pulling what's left of my hair out on this one. I would like to open a form using an id from a combo box and a search term from a text box. When the user enters a search term into a text box and clicks "Search" button, they should get a resulting page that returns all records the combo box matches an ID and the text box searches in multiple other fields for the term. The ID and one search term parsing one column is the easy part. When I try and add more columns to the search term using the "Or" command it is not working right. See my code below. I know it is just a syntax issue, but can't figure it out.



    This code gives me the result I am looking for where the first where clause is right and the description column is searched for the term in the search text box.
    DoCmd.OpenForm "frmInventoryList", acNormal, "", "[KingdomID] = [Forms]![frmComponents2]![KingdomID] And [Description] Like '" & "*" & [Forms]![frmComponents2]![txtSearch] & "*" & "'"

    What I need to do is have the first clause return as is and have multiple columns searched using the text box term. I thought the code below would work, but it returns everything.

    DoCmd.OpenForm "frmInventoryList", acNormal, "", "[KingdomID] = [Forms]![frmComponents2]![KingdomID] And [Description] Like '" & "*" & [Forms]![frmComponents2]![txtSearch] & "*" & "' or [FamilyDescription] Like '" & "*" & [Forms]![frmComponents2]![txtSearch] & "*" & "'"

    Thanks so very much for your help.

  2. #2
    guitarzycki is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    20
    After rereading it, I think it makes more sense to say that what I need is for the "Or" to be a part of the second clause, but it seems like it is acting as it's own clause all together.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You are confusing us with a combination of jargon and what with how. I suggest you tell us in simple English what you are trying to accomplish --perhaps you could provide a sample for clarification.
    Why do you need ID if you are searching for a text string within FamilyDescription? IT just isn't clear to me.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    And's and Or's do not play well together without parentheses. The best way to do this is to create a query and use what you have written as criteria. The resulting SQL will show you how it needs to be written.

    Ignoring errors, you can simplify the code by
    "KingdomID=" & Me!KingdomID & " AND Description Like '*" & Me!txtSearch & "*'"

  5. #5
    guitarzycki is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    20
    I guess in the simplest form, what I need to do is to have a search button that finds all records in a table that match a specific ID and a search term. On my form there is a search button and a text box. If the user clicks on a search button with no text in the box, a multiple items form opens and all the records that match the ID are displayed. That is the simple part. What I would like to do have is the user can add a search string to and have the search button open up the multiple items form where the ID matches along with searching several other columns in the query for the search term.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    guitarzycki is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    20
    aytee111 - You are a life saver! I had not even considered the use of a query to get a peek into the underlying SQL statement. Thank you very much for your help. It works like a charm now and seems like I was just missing double parentheses around the second clause.
    This is the code that works for what I need:

    DoCmd.OpenForm "frmInventoryList", , , "KingdomID =" & Me!KingdomID & " And ((Description Like '*" & Me!txtSearch & "*' or FamilyDescription Like '*" & Me!txtSearch & "*'))"

    Thanks you all, again for your help!

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

Similar Threads

  1. Replies: 3
    Last Post: 09-21-2016, 06:35 AM
  2. Replies: 3
    Last Post: 09-12-2016, 11:49 AM
  3. Openform with two criteria
    By prendergi in forum Forms
    Replies: 3
    Last Post: 05-20-2016, 03:32 PM
  4. Search in a Form with Wildcards
    By excellenthelp in forum Programming
    Replies: 6
    Last Post: 06-19-2014, 03:05 PM
  5. Search with multiple criteria
    By injanib in forum Forms
    Replies: 1
    Last Post: 01-16-2011, 11:51 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