Filter with multiple values

[paloma.rz]

Hi guys! my problem is refered to filtering a subquery with a list box with multiple select items. I saved all the data in a string, separating the values with comas, and then set the filter. But it wont work when i select more than one value.


The listbox has 3 strings to choose, "Ordinario", "integrazione", "Speciale", and the column name is "Categoria".
For now my code looks like this:

Private Sub btnSearch_Click()
Dim varItem As Variant
Dim strSearch As String
Dim Task As String


Me.DB_Milestone_subform.Form.FilterOn = False


For Each varItem In Me!ListCategoria.ItemsSelected
strSearch = strSearch & "," & Me!ListCategoria.ItemData(varItem)

Next varItem


If Len(strSearch) = 0 Then
Task = "Select * from DB_Milestone"
Else
strSearch = Right(strSearch, Len(strSearch) - 1)
MsgBox (strSearch)
Task = "Select * from DB_Milestone where ([ID] in ( " & strSearch & " ))"
'MsgBox (Task)
End If

'I created Task string but for now i couldnt use it on the filter


Me.DB_Milestone_subform.Form.Filter = " Categoria = ( '" & strSearch & "')"
Me.DB_Milestone_subform.Form.FilterOn = True


'DoCmd.ApplyFilter Task


'This part didnt even work. im new at programming


End Sub

Could anyone help me please??

[pbaldy]

You aren't using In for the filter, and text values need delimiters. This isn't exactly what you're doing, but building the filter would be the same:

http://www.baldyweb.com/multiselect.htm

note you need the text version.

[ranman256]

'----------------
sub FilterData_Click()
'----------------
dim sWhere as strin 

sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"

If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub

[pbaldy]

'----------------
sub FilterData_Click()
'----------------
dim sWhere as strin 

sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"

If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub

I can't see how this relates to the issue at hand.