Multi-value field filter question using strFilter

[Joe S.]

Currently I have a form that has some list boxes. One of the list boxes is a
multi-value multi-select and i'm having a bit of trouble getting it to work
with my strfilter.

Currently my code looks like this:
-------------------------------
Private Sub Command28_Click()

If Me.lstpayment.ItemsSelected.Count = 1 Then
strFilter = strFilter & "[payment_type] = '" & Me!lstpayment & "' And "
End If

If Me.lststore.ItemsSelected.Count = 1 Then
strFilter = strFilter & "[store_name] = '" & Me!lststore & "' And "
End If

If Me.lstkeyword.ItemsSelected.Count >= 1 Then
strFilter = strFilter & "[keywords] = '" & Me!lstkeyword & "'"
End If


DoCmd.OpenReport "rptnopic", acViewPreview, , strFilter

End Sub
--------------------------

The lstkeyword is the multi-value multi-select listbox. Every time i run
the filter, i get a error
"the multi-valued field '[keywords]" cannot be used in a WHERE or HAVING
caluse.

how can i modify my code to fix this error? Also, i can make it single
select if that will make it easier, but it needs to remain multi-value.

Any help would be appreciated.

If i remove the lstkeywords from the filter and run it based off the other 2
list boxes it works perfectly.

 

[Douglas J. Steele]

As soon as you make a list box multiselect, you lose the ability to refer to
the control in the way you are. Instead, you have to loop through the list
box's ItemsSelected collection (even if only one row is selected).

Dim strKeywords As String
Dim varSelected As Variant

If Me.lstkeyword.ItemsSelected.Count >= 1 Then
For Each varSelected In Me!lstkeyword.ItemsSelected
strKeywords = strKeywords & "'" & _
Me!lstkeyword.ItemData(varSelected & "', ")
End If
strKeywords = Left(strKeywords, Len(strKeywords) - 2)
strFilter = strFilter & "[keywords] IN (" & strKeywords & ") And "
End If

 

[JoeS]

Doug thanks for the reply, i tried using the code you provided and i'm getting a "end if without block if" at the first End If in the middle of the code.