MS Access Text Field Special Characters

Question

I have a search button that executes this applyfilter macro code:

[ContractNumber] Like "*" & [Forms]![frmContractMatch&Revenue]![Text44] & "*"

So it searches text box text44 for the appropriate ContractNumber. The trouble I am having is that if a contract number is ART#45 for example, it will not find it in the database because of the special character #.

How do I get around this? The problem is that the contracts could have different special characters or they may have none at all, so I can't tell it to specifically search for a particular symbol all the time.

Answer 1

Access uses the # as a wildcard for a single number field. When you create a search field and include #, such as ART#, Access interprets this as "I'm searching for the string 'ART' followed by a single numeric character 0-9" It uses a '?','%', and '-' similarly. To get around this, you need to alter the input string and enclose the literal special character in square brackets. so instead of searching for:

'ART#'

You would search for:

'ART[#]'

how do you get around this? depends on how you are entering the search criteria. This area is not really my strong suit, but depending on how the search is entered you could probably use a custom function to add the square brackets to your parameter before filtering the form. I personally would prevent the use of special characters in the order number thru code.