VBA filter not working in form "Mark Kubicki" <Mark[ at ]TillotsonDesign.com> 11/26/2008 10:24:25 PM 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

RE: VBA filter not working in form Beetle 11/26/2008 11:18:02 PM 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 -- _________ Sean Bailey "Mark Kubicki" wrote: [Quoted Text] > 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 > > > >

Re: VBA filter not working in form Marshall Barton <marshbarton[ at ]wowway.com> 11/27/2008 12:17:21 AM Mark Kubicki wrote: [Quoted Text] >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 & ")" -- Marsh MVP [MS Access]