Filtering reserved characters

[Ray C]

I recently found that my database crashed if I ever searched for a record
with "&" in the title. E.G If strField = "SupplierName" and varInfo =
"Brighton & Hove", when it has been processed by the following line

strCriteria = BuildCriteria(strField, dbText, varInfo)

gets converted to
"SupplierName = "Brighton" & "Hove"" and the search Fails.
Similarly "Brighton and Hove" gets converted to
"SupplierName = "Brighton" and "Hove"" and also fails the search
However "Brighton - Hove" gets converted to
"SupplierName = "Brighton - Hove"" and is found.

Note the position of the quotation marks.

(All the above examples assume that the information contained in the fields
exists as stated)

I assume that "&", "and" are reserved characters but how would I get round
the problem?

Thanka Ray C

 

[John Spencer]

You need to force the quotes around the string to make it work reliably. And
of course if the string has embedded quotes then you are going to need to
double up on embedded quotes.

The following should work
varInfo= Chr(34) & "Brighton & Hove" & Chr(34)

Of course, that complicates life a bit if you tried to pass in
Brighton Or Hove Or Johnson
and wanted to get back
SupplierName = "Brighton" Or SupplierName="Hove" or SupplierName="Johnson"

SO you would need to have something that detected when to add the quotes and
when not to add the quotes. Perhaps testing for the presence of the strings
" AND " or "&" and also check quotes.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer]

Of course there are a few others that will give you unexpected results or an
error. Some of them are

, <, <>, Or, +, *, ? , []

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Ray C]

Brilliant John, I never thought of using the old chr(34) ploy.
Thank you so much (and thanks for the warnings on the other problem char's)

rayC