Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2014
    Posts
    9

    Unanswered: Find string from Multi-select listbox in query

    I have a form (frm_ShipSearchNew) with a listbox (lstShipName) from a table (SHIP_SELECT.NAME)

    I want the user to be able to select one or more items from the list then search anywhere in a field in a query called "[qry_Test]!"[SHIP_SEARCH]"
    This qry is made up from the table "DrawingData".

    I have this code on my "on click" SEARCH BUTTON:

    Private Sub CmdSrchDraw_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String

    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qry_Test")
    For Each varItem In Me!lstShipName.ItemsSelected
    strCriteria = strCriteria & ",'" & Me!lstShipName.ItemData(varItem) & "'"
    Next varItem
    If Len(strCriteria) = 0 Then
    MsgBox "You did not select anything from the list." _
    , vbExclamation, "Nothing to find!"
    Exit Sub
    End If
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)
    strSQL = "SELECT* FROM DrawingData " & _
    "WHERE DrawingData.SHIP_SEARCH IN(" & strCriteria & ");"
    qdf.SQL = strSQL
    DoCmd.OpenQuery "qry_Test"
    Set db = Nothing
    Set qdf = Nothing

    DoCmd.Close acForm, "frm_ShipSearchNew", acSaveNo
    End Sub

    The command works fine if I'm searching for one name exactly the way it's listed. However I want to find multiple names anywhere in the text field.

    I'm new to this VBA stuff so the 1st grade answer would be helpful!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so presumably you are suing a multi select list or combo box.
    this shows you how to retrieve the selected items in a multi select list/combo box
    stuffing the value(s) from the list box selected property into strCriteria as a comma delimited list should achieve what you want. however that will only work for an exact match.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2014
    Posts
    9
    Yes I'm using a multiselect listbox but I need to be able to find the selected item anywhere in a text/ string field.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    In which case you cannot use the in predicate. Each selected item needs to be matched usining like with wildcards. Eg

    where ship_search like "*item1*" or ship_search like "*post*" and so on.....
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2014
    Posts
    9
    I'm sorry. I don't understand. Am I going about this all wrong?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    not totally wrong...

    you cant use the in predicate .(where column in(1,2,4,8) if you want to find something that contains what you are lookign for

    ferinstnace
    where userid in ("Smith", "Jones", "O'Reilly") works
    but that measn where the user.id is either Smith, Jones or O'Reilly

    if you want to find rows whose user id includes mith, jon or O'Re then you'd need
    where (userid like "*mith*"
    or userid like "*jon*"
    or userid like "*O\'R*")
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2014
    Posts
    9
    So you're saying I need to edit this line?

    "WHERE DrawingData.SHIP_SEARCH IN(" & strCriteria & ");"


    >>>I'm sorry. I told you I'm not very good at this. I'm just sort of figuring it out as I go along.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    You cannot use the in construct if you want to do a partial string match. If its an exact match then the in construct is fine.

    if you want to find rows which, say, include post in the ship_search and post is one word in many then you have to use the like construct. Eg
    where ship_search like "*post*"
    This will return rows where the word post appears somewhere in ship_search. That could be the word post, or postal or fencepost.
    because you are using a multi sekect list box you need or add an or sub clayse for each ittm selected
    where ( ship_search like "*post*"
    Or ship_search like "*courier*
    Or ship_search like "*parcel*")

    note the use of brackets to group together the or options.
    read up on the use of wild cards in Access.
    read up on the use of the like clause in SQL in Access
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Aug 2014
    Posts
    9
    I understand the use of wildcards in Access in a query. What I don't understand is the use of wildcards in a VBA command. I posted what I was using as an example. My question is can I change the script slightly to include wildcards or do I need to take another approach?

    You said I couldn't use the "IN" argument. I'm assuming you're referring to this line:
    "WHERE DrawingData.SHIP_SEARCH IN(" & strCriteria & ");"

    Is there a simple way to change this line so that whatever the user selects in the multiselect listbox will be found?

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Your vba is, in part constructing a SQL statement. Its a SQL statement that extracts the data from the db.
    there is no other way to find a word / group of symbols within a column of a db without using the like clause. You most definately cannot use the in construct. As said before you can use the in construct providing its a complete match.
    I'd rather be riding on the Tiger 800 or the Norton

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
  •