Results 1 to 6 of 6
  1. #1
    unclechid is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    3

    Selecting multiples using LIKE but retrieved from a form

    I have a form that has a drop down combo box field that I use to select an airplane type. That selection (just 1 as I don't allow you to select more than 1) then is used to compare to a table that was imported from another source. The query works when there is only 1 item in the field [FleetType] but when there is more than 1 FleetType I tried using a wildcard. See below for example of what works, doesn't work.

    Field [FleetType] Values record 1 = A319;#A320;#A321;#B737;#B757;#B767
    record 2 = A319

    In the criteria if I use "A319" it returns 1 value --> record 2 which is correct and expected
    In the criteria if I use LIKE "*A319*" it returns 2 values --> records 1 and 2 which is correct and expected

    BUT if the criteria I use is [Forms]![NewSearch]![cboFleetType] AND I only select A319 I get 1 record --> record 2 which is what is expected HOWEVER, I want to include all records that have A319 as one of the fleet types SO....
    in respect to the 2nd situation above I use criteria LIKE "*[Forms]![NewSearch]![cboFleetType]*" and I get nothing....

    Why doesn't the wild card using the form value/reference not work? I even built a new field on the form that is a copy of what is selected from the combo drop down to "get the value away from the cbo field" and to confirm what value was being selected and that also doesn't work.

    HELP PLEASE

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try

    LIKE "*" & [Forms]![NewSearch]![cboFleetType] & "*"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    if you use a listbox, then you can select multiple items
    but the query would have to be vb coded, to make all the likes:

    Code:
    sub btnMakeSql_click()
    dim i as integer
    dim sWhere as string
    dim qdf as querydef
    const Q = """"
    
    For i = 0 To Me!lstBox.ListCount-1
        if Me!lstBox.Selected(i) then
           sWhere = sWhere & " and [FleetType] like " & Q & "*" & lstBox.ItemData(i) & "*" & Q
        endif
    Next 
    
       'build the query
    
    set qdf = currentdb.querydefs("qsMyQry")
    qdf.sql = "Select * from table " & swhere
    qdf.close
    docmd.openquery qdf.name
    
    end sub

  4. #4
    unclechid is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    3
    Genius!

    That was the trick.

    Thank you!

  5. #5
    unclechid is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    3
    Excellent! I am sure this will be the next ask.

    Thank you

  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
    Happy to help and welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Choosing Multiples in a form
    By Jackfam58 in forum Forms
    Replies: 1
    Last Post: 07-31-2012, 08:41 AM
  2. Replies: 1
    Last Post: 01-27-2011, 04:02 PM
  3. Query criteria retrieved from a Form's listbox
    By blacksaibot in forum Programming
    Replies: 1
    Last Post: 01-29-2010, 10:38 AM
  4. Query criteria retrieved from a Form combobox?
    By blacksaibot in forum Queries
    Replies: 1
    Last Post: 01-27-2010, 10:18 AM
  5. Replies: 3
    Last Post: 07-30-2009, 07:12 AM

Tags for this Thread

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