Apostrophe in the data interferes with code

[hilian]

I'm setting up data entry through a a combo box. The data are names of social services agencies. Some of these agency names have apostrophes in them, for example, Children's Aid Society, or St. Vincent's Services. In testing the combo box, when I come to one of these agency names, I get this error message: "Syntax error (missing operator in query expression). I tried enclosing the names in the table that feeds the combo box with double quotes, but that didn't work. How is this handled? I could simply remove all of the aprostophies, but I'm sure there's a better way.

Thanks,

Henry

 

[rodmc]

http://msdn.microsoft.com/en-us/library/bb256508(v=office.12).aspx

 

[Pat Hartman]

Whenever a field may contain a value that includes a single quote, you must enclose the string in double quotes or duplicate the single quote. So:

"O'Neil"
OR
'O''Neil'

What I do is to create a constant named QUOTE that contains a double quote. Then I use that when building strings.

strSQL = " WHERE LastName = " & QUOTE & Me.LastName & QUOTE
That will result in a string like:
WHERE LastName = "O'Day"

 

[hilian]

Pat, thanks. That's the answer I was looking for.