Multiselect Listbox Query Code

[NHMM]

I am trying to use a multiselect listbox in a query. I have the following On
Click event procedure in an attempt to pass the list to the query as criteria:

Private Sub Command3_Click()
Dim mFilter As String
Dim i As Variant
Dim c As Integer
c = 0
For Each i In List0.ItemsSelected
If c = 0 Then
mFilter = "'" & List0.ItemData(i) & "', "
Else
mFilter = mFilter & "'" & List0.ItemData(i) & "', "
End If
c = c + 1
Next i
mFilter = Left(mFilter, Len(mFilter) - 2)
mFilter = "ID_INDUSTRY in (" & mFilter & ")"
DoCmd.OpenQuery "Query1"
DoCmd.ApplyFilter , mFilter
End Sub

I am getting a Run-time error 2501 and the ApplyFilter action is cancelled.
The debugger is having problems with the DoCmd.ApplyFilter line.
Any ideas?
Thanks
NHMM

 

[NHMM]

Got it.... because the filtering column was a number and not text I had to
remove the quotes and made a few other changes.
The final version of the code looks like this if anyone is intersted:

Private Sub Command3_Click()
Dim mFilter As String
Dim i As Variant
Dim c As Integer
c = 0
For Each i In List0.ItemsSelected
If c = 0 Then
mFilter = List0.ItemData(i) & ","
Else
mFilter = mFilter & List0.ItemData(i) & ","
End If
c = c + 1
Next i
mFilter = Left(mFilter, Len(mFilter) - 1)
mFilter = "ID_INDUSTRY in (" & mFilter & ")"
MsgBox mFilter
DoCmd.OpenQuery "Query1"
DoCmd.ApplyFilter , mFilter
End Sub

 

[Dale Fye]

you might even want to try this:

if c= 1 then
mFilter = "ID_INDUSTRY = " & mFilter
else
mFilter = "ID_INDUSTRY in (" & mFilter & ")"
endif

This way, if there is only one item selected, the query will use the =
comparison, rather than IN ( ), which should run quicker.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.