Filter Question

[data808]

I don't really know much about delimiters but I'm thinking my problem may have something to do with that. I am using the VBA below behind a filter button. The cmbOutageInvestigation is a drop down box that has a list of options to choose from. Some of those options contain a special character such as the # sign for example. I noticed this VBA does not filter the options that have these special characters in them. The form just goes blank. It's a split form by the way, with an unbound combo box. Anyway, does anyone here know how to make this code work for regular text mixed with special characters? Example, if I wanted to search for something like: "John Doe-Clerk #2 Department 5" without the quotes of course.

Me.Filter = "[Outage Investigation] Like '*" & Me.cmbOutageInvestigation & "*'"
Me.FilterOn = True
Me.Requery
Me.cmbOutageInvestigation.SetFocus

Thanks.

[hapm]

Some informations about the text comparission used by LIKE: http://msdn.microsoft.com/en-us/library/bb221192 . The important thing for you:

You can use the special characters opening bracket ([ ), question mark (?), number sign (#), and asterisk (*) to match themselves directly only if enclosed in brackets.

What that means for you is that you need to replace # by [#]:

Me.Filter = "[Outage Investigation] Like '*" & Replace(Replace(Me.cmbOutageInvestigation, "[", "[[]"), "#", "[#]") & "*'"

You can add more replaces for * and ? but I commonly not escape them to make the filter form more powerful. The charlist "[" and the digit matcher # can get in handy as well, but be sure your users know how to use them properly. Perhaps with a reference to the link above from your filters form to allow them to look up the syntax.

[data808]

This is wonderful. Thank you so much for the help. Its strange that you have to create a specific portion of the code for each special character that you want to add to the filter. Is there a simple way to include everything that you are able to type with a keyboard to be in the filter? If not, thanks anyway for what you have provided. It works great.

[hapm]

As mentioned in the documentation you only need to escape the ?, *, [ and #. All other characters don't need any special handling and will be matches as they are. You need to escape these 4 because else the engine will try to interpret them as mentioned on the side I linked to.