Adjusting SQL to accommodate apostrophe

[aziz rasul]

I have the following code

strSQL = "UPDATE tblEnquirers SET tblEnquirers.Comments = [Comments] & ' The enquirer was removed from the FABtots sessions for the """ & strGroupName & """ group on " & Format(Date, "dd/mm/yy") & ".' WHERE (((tblEnquirers.enquirerID)=" & lngEnquirerID & "));"
CurrentDb.Execute strSQL

Everything work fine if strGroupName is

FABtots - Childrens & Play Centre

But if strGroupName is

FABtots - Children's & Play Centre

then it doesn't as I have an apostrophe in the strGroupName.

How do I amend the SQL code to accommodate this and potentially any other irregular characters?

 

[boblarson]

That's where CHR(34) comes in handy:

strSQL = "UPDATE tblEnquirers SET tblEnquirers.Comments = [Comments] & [B][COLOR=red]CHR(34) & [/COLOR][/B] [COLOR=red][B]" [/B][/COLOR]The enquirer was removed from the FABtots sessions for the [COLOR=red][B]" & Chr(34)[/B][/COLOR] & strGroupName & Chr(34) & " group on " & Format(Date, "dd/mm/yy") & [COLOR=red][B]Chr(34) &[/B][/COLOR] ". WHERE (((tblEnquirers.enquirerID)=" & lngEnquirerID & "));"

CurrentDb.Execute strSQL

 

[boblarson]

Not only does it handle the single quotes but it helps you out with making it more easily distinguished where the quotes go where the triple double quotes can be mighty confusing.

 

[aziz rasul]

I'm getting a compile error i.e.

Expected: end of statement

where 'The' is highlighted!

 

[jal]

I use parameters to avoid all these issues. But if you insist on doing things the hard way, here's a couple of pointers:

- Dates have to be in pound signs #01/01/1905#
- You can't have a period before a WHERE clause
". WHERE ....

 

[jal]

Best advice - learn to user parameters !!! Then you won't have to worry about pound signs and apostrophes anymore.

 

[boblarson]

Then you won't have to worry about pound signs and apostrophes anymore.

Not really 100% accurate. You still need to learn when and where to use them or else you'll just be flapping like a piece of cloth in the wind.

 

[jal]

Not really 100% accurate. You still need to learn when and where to use them or else you'll just be flapping like a piece of cloth in the wind.

True, I realize now I overstated the point. Fair enough.