Using values from multi-select list box in MS Access Query

Question

I have one table in my MS Access data base named COA_Map. I also have a form with a multi-select list box. When the values are selected, vba turns them into a string and updates a text box with the string. I want to use the textbox string as a variable in a query.

This is my query:SELECT * FROM COA_Map WHERE (COA_Map.ASL IN ( [Forms]![Multi-Select Search]![TextASL].Text ) ); This returns empty results. When I copy and paste the text box values into the query like this:

SELECT * FROM COA_Map WHERE (COA_Map.ASL IN ( 2.1,2.3,2.4  ) );

I get the expected results. I tried [Forms]![Multi-Select Search]![TextASL].Value and [Forms]![Multi-Select Search]![TextASL] but that gives an error "This expression is typed incorrectly, or it is too complex"

I also tried using "OR" clause instead of "IN". I changed the VBA to return this string:

to build this query: SELECT * FROM COA_Map WHERE COA_Map.ASL = [Forms]![Multi-Select Search]![TextASL] ;

This returns the same empty results. When I paste the textbox values into the query like this: SELECT * FROM COA_Map WHERE COA_Map.ASL = 2.1 OR COA_Map.ASL = 2.2 OR COA_Map.ASL = 2.3 ; , I get expected results.

When only one value is selected in either version of the query, I get expected results.

I can't figure out why the the query will not return results when reading from the text box with multiple values selected.

ASH · Accepted Answer · 2019-12-22 23:19:26Z

Here is a generic example to get you going in the right direction.

Private Sub cmdOK_Click()
' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
' Get the database and stored query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryMultiSelect")
' Loop through the selected items in the list box and build a text string
    For Each varItem In Me!lstRegions.ItemsSelected
        strCriteria = strCriteria & ",'" & Me!lstRegions.ItemData(varItem) & "'"
    Next varItem
' Check that user selected something
    If Len(strCriteria) = 0 Then
        MsgBox "You did not select anything from the list" _
            , vbExclamation, "Nothing to find!"
        Exit Sub
    End If
' Remove the leading comma from the string
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)
' Build the new SQL statement incorporating the string
    strSQL = "SELECT * FROM tblData " & _
             "WHERE tblData.Region IN(" & strCriteria & ");"
' Apply the new SQL statement to the query
    qdf.SQL = strSQL
' Open the query
    DoCmd.OpenQuery "qryMultiSelect"
' Empty the memory
    Set db = Nothing
    Set qdf = Nothing
End Sub

Collectives™ on Stack Overflow

Using values from multi-select list box in MS Access Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related