using a '[' in a string of a sql statement

[Happy YN]

I have a search run from a from which uses text typed by the user in a textbox as its criteria.
However if the user chooses to search for a string that includes a '[' or ']' then the sql runs into problems since by its nature the sql contains many '[' and ']' e.g. around tablenames and fieldnames etc
Any ideas ?
Do I first have to convert the string into chr(?) and search like that & would it work?
Thanks

 

[tadpole]

parameter queries

because you are using a wildcard to look for a wildcard character you will need to close the wildcard in brackets.

to find [bob] in a text field

use *[[bob]]

hope this works

 

[dcx693]

you are using a wildcard to look for a wildcard character

That's not strictly true since the [ character is not a wildcard. It's a delimiting character not unlike the " and the ' characters.

To see if a string contains the [ character, use this expression:
yourstring Like "*[[]*"
If will return True if it has the string, False otherwise.

Likewise, if you want to see if a string contains the [, %, # or * characters, use:
yourstring Like "*[[%#*]*"
It will return True if the string contains any one of those characters.

The one exception is the ] character. It can only be searched for using this expression:
yourstring Like "*[]]*"
You cannot have it searched for in combination with other characters.

You may be also able to search using the Chr code of the characters. I don't see why not, but I haven't tried that.

 

[Happy YN]

Thats good but as far as the user is concerned they want to be able to type in [london] in the textbox and find all the records whose city field is identical i.e. [london] (not london)
so what do I tell them to type in?
Thanks

 

[Happy YN]

Just discovered that *london] brings out the requred records since I have the LIKE operator in the SQL
Thanks everyone for your help