VBA filter not working in form

[Mark Kubicki]

I have the following code set up to filter a form, but the results are
always blank...

any suggestions on what might be going on?

lsFilter is a listbox with a simple mulit select
the values are a string
the field [Catagory] is in the data source for the form and is also a string

strDelim = """"
With Me.lstFilter
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & LTrim(.ItemData(varItem)) &
strDelim & " or " 'build the filter criteria
End If
Next
strWhere = Left(strWhere, Len(strWhere) - 4) ' clean up the trailing
"or"
End With

Me.Filter = "[Catagory] = '" & strWhere & "'"
Me.FilterOn = True

as always, thanks in advance,
mark

 

[Beetle]

Your strWhere variable is already delimited with quotes, so delimiting
it again in the Filter criteria may be causing your problem. Try

Me.Filter = "[Category]=" & strWhere

 

[Marshall Barton]

I have the following code set up to filter a form, but the results are
always blank...

any suggestions on what might be going on?

lsFilter is a listbox with a simple mulit select
the values are a string
the field [Catagory] is in the data source for the form and is also a string

strDelim = """"
With Me.lstFilter
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & LTrim(.ItemData(varItem)) &
strDelim & " or " 'build the filter criteria
End If
Next
strWhere = Left(strWhere, Len(strWhere) - 4) ' clean up the trailing
"or"
End With

Me.Filter = "[Catagory] = '" & strWhere & "'"
Me.FilterOn = True

You have been led astray by the query designer. What you
are constructing is not a legal filter string.

You can use two different syntax rules for what you want,
either:
field = val1 OR field = val2 OR ...
or by using the shorter wxpression using the IN operator:
field IN(val1, val2, ...)

I prefer the second approach using something like this air
code:

With Me.lstFilter
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & "," & strDelim &
LTrim(.ItemData(varItem)) & strDelim
End If
Next
strWhere = Mid(strWhere, 2) ' clean up the leading
comma
End With

Me.Filter = "[Catagory] IN(" & strWhere & ")"