Query a form on a Multi Select List box (1 Viewer)

[jco23]

I have a continuous form with comboboxes that feed into a query. In addition to the comboboxes, I'd like to add a multi select list box that enables the user to filter the form (query results) based upon the selections.

For reference, the field that I would like to filter on is also referenced in the query code via combobox.

However, each time I attempt to requery, I get a syntax error (missing operator) on the Criteria.

the name of the multi select list box is "status_list", and the field that I am looking to filter on is "status_cd" (this is the name of the textbox and control within the form).

hope I gave enough detail, so if anyone needs more, let me know.

thanks!

the code I have is:

Dim Criteria As String
Dim i As Variant

Criteria = ""

For Each i In Me![Status_List].ItemsSelected
Criteria = Criteria & "," & Me![Status_List].ItemData(i)
Next i

Criteria = Mid(Criteria, 2)

Criteria = "Where status_cd In(" & Criteria & ")"

Me.Filter = Criteria
Me.FilterOn = True


Me.Requery
Me.Refresh

 

[arnelgp]

if status_list is string(text):

Criteria = Criteria & "," & Chr(34) Me![Status_List].ItemData(i) & Chr(34)

Criteria = "status_cd In (" & Criteria & ")"

Me.Filter = Criteria
Me.FilterOn = True


Me.Requery
Me.Refresh

 

[jco23]

if status_list is string(text):

Criteria = Criteria & "," & Chr(34) Me![Status_List].ItemData(i) & Chr(34)

Criteria = "status_cd In (" & Criteria & ")"

Me.Filter = Criteria
Me.FilterOn = True


Me.Requery
Me.Refresh

thanks for the quick response. to while that did add the double quotes to the each list item (which I noticed was missing), I'm still getting the same error. I'm thinking that it is somewhere in the Criteria part. it claims that the error is in the expression, 'where status_cd In("Approved")'.

also, had to enter an extra "&" symbol before the Me! in the code you kindly provided

 

[arnelgp]

For Each i In Me![Status_List].ItemsSelected
Criteria = Criteria & "," & Chr(34) Me![Status_List].ItemData(i) & Chr(34)
Next i

Criteria = Mid(Criteria, 2)

Criteria = "status_cd In (" & Criteria & ")"

Me.Filter = Criteria
Me.FilterOn = True


Me.Requery
Me.Refresh

 

[jco23]

For Each i In Me![Status_List].ItemsSelected
Criteria = Criteria & "," & Chr(34) Me![Status_List].ItemData(i) & Chr(34)
Next i

Criteria = Mid(Criteria, 2)

Criteria = "status_cd In (" & Criteria & ")"

Me.Filter = Criteria
Me.FilterOn = True


Me.Requery
Me.Refresh

 

[moke123]

this function will return a comma delimited string from your listbox selections

Public Function getLBX(lbx As ListBox) As String    'get a comma delimited string of selected items

    Dim strList As String
    Dim varSelected As Variant

    If lbx.ItemsSelected.Count = 0 Then
        'MsgBox "Nothing selected"
    Else

        For Each varSelected In lbx.ItemsSelected

            strList = strList & lbx.Column(0, (varSelected)) & ","

        Next varSelected

        strList = Left$(strList, Len(strList) - 1)  'remove trailing comma

    End If

    getLBX = strList

End Function

heres a sample db using this function to get the selected items. It also has a function to select the stored values back to the listbox.