MS Access Search split Form with unbound listbox

Question

I am a novice in MS access and struggling to come up with a correct code lines to enable a multiparameter search form. I have a query that collects info from 3 tables. Two of the fields in this query are a cascade combobox and a listbox. The listbox updates (by Requery) from the combobox. For each value in combobox, there are 3-5 values in the list box. I tried to set up a search form (splitform with spreadsheet) where the Unbound combobox and listbox would find all the values that were selected in the listbox and show the resulting pool of values in the spreadsheet of the splitform. The combo-listbox and the form are working OK to show all initial values and works OK when only ONE of the values was selected in the list box. However, when more than 1 value is selected in the listbox, I get a message

Syntax error (comma) in query expression '(GistID = (4,9))'

or similar depending on the GistName ID#.

The code for this form:

Private Sub cmdSearch_Click()
    Dim varItem as Variant
    Dim strSearch As String
    Dim Task as String

    For Each varItem In Me!listbGist.ItemsSelected
        strSearch=strSearch & "," & Me.listbGist.ItemData(varItem)
    Next varItem
    If Len(strSearch)=0 Then
        Task="select * From qryScreening"
    Else
        strSearch=Right(strSearch, Len(strSearch)-1)
        Task="select * from qryScreening where (GistID=("& strSearch & "))"
    End if
    DoCmD. ApplyFilter Task
End Sub

The code generates a string ,X,X,X and the Len function removes the first comma as I learned via MsgBox(strSearch) but the filter Task does not run if more than 1 value was selected in the listbox. Could you please suggest a remedy to this code that is not working now.

The = needs to be in so like this select ….. where x in (1,2,3,4,5) — Nathan_Sav
Thank you. The IN did work but not at once! Turned out there should be no space between In and ( and then it worked! where SGistID IN(" & strSearch & ")" — AM7223
@Nathan_Sav, sounds a valid answer if you want to write it up. AM7223, tag the comma on at the end instead of beginning and don't have to bother with trimming. Trailing comma in series will be ignored. — June7

Nathan_Sav Nathan_Sav · Accepted Answer · 2019-11-27T10:23:49

You need to use the SQL IN or build an OR statement. IN would be more readable.

like so:

The = needs to be in so like this select * from [qryScreening] where [GistID] in (1,2,3,4,5)

Also, may read a bit better to check the selected items count rather than a reliance on the string length.

MS Access Search split Form with unbound listbox

1 Answers