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

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
Dec 7, 2009
Reaction score
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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question