Using VBA to filter many fields for a keyword

[Redbeard]

I am trying to create button that runs a filter in VBA that search several fields (Combo boxes and memo fields) for a key word. I started off just filtering one combo box with only a single word in it using this code and it works!

Private Sub Apply_Filter_Click()
Dim MyValue As String
MyValue = InputBox("Enter Keyword")
Me.Filter = "[Field1] = '" & MyValue & "'"
Me.FilterOn = True
End Sub
 

I am now attempting to filter a memo field but can’t get the code to work? See Below.

Private Sub Apply_Filter_Click() 
Dim MyValue As String 
MyValue = InputBox("Enter Keyword") 
Me.Filter = "[Field1] = '*" & MyValue & "*'"
Me.FilterOn = True 
End Sub 
 

I have tried several different variation of this code with the * in a different spot and I either get errors or no results? Anyone see what I am doing wrong? And how do I add more fields on to the filter? Do I just use "Or" and repeat the code?

[TheSmileyCoder]

Filters are basicly a WHERE clause with the Where keyword. So if what you write won't work in the query designer it won't work in the filter either.

Now when using wildcards you can not use the equality (=) operator, but must use the LIKE keyword instead:

Me.Filter = "[Field1] Like '*" & MyValue & "*'"

Moving on, yes, you can just add more fields to your code using OR between each comparison.

[Redbeard]

Thanks TheSmilyCoder that worked!
However, I am having trouble with adding additional fields with "or"? I thought it would be simple as just adding the "or" in-between statements but I am obviously missing something as I get a Type Mismatch Error 13. What am I missing?

Me.Filter = "[Field1] Like '*" & MyValue & "*'" Or "[Field2] Like '*" & MyValue & "*'"

[TheSmileyCoder]

The or you have in your current syntax is placed between the evaluation strings, not as part of it.

Try this:

Me.Filter = "[Field1] Like '*" & MyValue & "*' Or [Field2] Like '*" & MyValue & "*'"

[Redbeard]

It worked! I am still trying to learn how VBA works and it is hard to wrap my head around the syntax. Thanks again TheSmilyCoder for all the help!

[zmbd]

You might find the following helpfull:
Microsoft Access / VBA Insights Sitemap
42.Cascaded Form Filtering
55.Multiple Combobox Filter with Clear Option
168.Example Filtering on a Form.