problematic use of apostrophy in SQL statement

[Philip Leduc]

I need to solve the following problem:
To do filters on a recordset I use a SQL statement, the advantage is that I
can then use that statement to change the recordsource of a subform that
uses the same query as that recordset, a report that uses the same recordset
or another form that uses the same recordset.
strSQL = "SELECT * FROM QrycustomersReport WHERE [cuscompanyName] like '*" &
Me.txtChooseNameLike & "*'"

(I also use this statement in a more complex form with a where clause that
can include multiple checks that results in a composed statement such as
strSQL = "SELECT * FROM QrycustomersReport WHERE [cuscompanyName] like '*" &
Me.txtChooseNameLike & "*' AND [custype] = " & me.cboChoosetype

This works great until the company name (Me.txtChooseNameLike) contains an
apostrophy! Does anyone see a solution?

 

[Ken Sheridan]

Rather than using single quotes (apostrophe) character use a double set of
double quotes characters when building the string expression. A double set
of quotes within a delimited string is interpreted as a single literal quotes
character:

"SELECT * FROM QrycustomersReport WHERE [cuscompanyName] like ""*" &
Me.txtChooseName Like & "*"" AND [custype] = " & me.cboChoosetype

You can also use the Chr(34) function call, 34 being the ANSI code for the
double quotes character:

"SELECT * FROM QrycustomersReport WHERE [cuscompanyName] like " & Chr(34) &
"*" & Me.txtChooseName Like & "*" & Chr(34) & " AND [custype] = " &
me.cboChoosetype

This assumes there are no embedded double quotes characters in the company
name of course! Its unlikely, but should that be the case you'd need to
write your own function which returns a string in which the quotes characters
in the value passed into the function are replaced with some other character
(the tilde is commonly used) and apply the function to the values both of the
txtChooseName text box and the cuscompanyName column. You then compare the
return values of the function rather than the values of the text box and
column.

Ken Sheridan
Stafford, England

 

[Delordson Kallon]

Hi Philip,

You have come across the famous apostrophy problem. One solution is to use
the replace function to replace all apostropies with double apostrophies.
Something like this...

strSQL = "SELECT * FROM QrycustomersReport WHERE [cuscompanyName] like '*" &
Replace(Me.txtChooseNameLike , "'", "''") & "*' AND [custype] = " &
me.cboChoosetype

i.e. replace single apostrophy (') with with a pair of apostrophies ('')
both enclosed in double quotation marks. Look up help on the replace function
for more info.

HTH
Delordson Kallon
www.instantsoftwaretraining.com

 

[John W. Vinson/MVP]

I need to solve the following problem:
To do filters on a recordset I use a SQL statement, the advantage is that
I can then use that statement to change the recordsource of a subform that
uses the same query as that recordset, a report that uses the same
recordset or another form that uses the same recordset.
strSQL = "SELECT * FROM QrycustomersReport WHERE [cuscompanyName] like '*"
& Me.txtChooseNameLike & "*'"

(I also use this statement in a more complex form with a where clause that
can include multiple checks that results in a composed statement such as
strSQL = "SELECT * FROM QrycustomersReport WHERE [cuscompanyName] like '*"
& Me.txtChooseNameLike & "*' AND [custype] = " & me.cboChoosetype

This works great until the company name (Me.txtChooseNameLike) contains an
apostrophy! Does anyone see a solution?

If you can safely assume that txtChooseNameLike will never contain a
doublequote character " then you can delimit the criteria string with "
rather than with '; to do so, use two consecutive doublequotes within the
doublequote delimited string constant:

strSQL = "SELECT * FROM QrycustomersReport WHERE [cuscompanyName] like ""*"
&
Me.txtChooseNameLike & "*"""

This will evaluate to

like "O'Niell"

which will search correctly.

If you might have criteria containing doublequotes (e.g. a company named
Joe's "Eclectic" Establishment), you can replace the ' with two consecutive
':

strSQL = "SELECT * FROM QrycustomersReport WHERE [cuscompanyName] like '*" &
Replace(Me.txtChooseNameLike, "'", "''") & "*'"

For readability, that is one apostrophe between quotemarks as the second
argument of Replace, and two consecutive apostrophes between quotemarks as
the third.