DoCmd.RunSql

[GUEST]

I've been having a problem with the following DoCmd.RunSQL. I've shortened up a bit for here. I've concluded that the problem is with the data "Carl's Friend". Apparently, the appostrophe in the data is throwing the program off. How can I incapsulate this string of data so that it doesn't confuse the SQL statement?

I'd appreciate any comments.

DoCmd.RunSQL "UPDATE Employees " & _
"SET Employees.Comments = 'Carl's Friend' " & _
"WHERE ((([Employees].[Employee_ID])= 2 &quot);"

 

[JohnLowell]

I use character strings to create the SQL:

DoCmd.RunSQL "UPDATE Employees " & _
"SET Employees.Comments = " & chr(34) & "Carl's Friend" & chr(34) & _
" WHERE [Employees].[Employee_ID]= 2;"

 

[RJFost]

As a rule of thumb, I avoid apostrophes (ASC 39) like the plague. Unless you're careful, it'll wind up that Access sees it as a delimiter for a string literal, then pitches a fit about bogus syntax in the query (e.g., in your example, trying to interpret "S FRIEND WHERE..." as SQL code.

 

[jebry]

Hi!

If the Chr(34) trick doesn't work for you then you can do this:

In a module define

Public Const Quote = """"

Your code will be:

DoCmd.RunSQL "UPDATE Employees " & _
"SET Employees.Comments = " & Quote & "Carl's Friend" & Quote & _
" WHERE [Employees].[Employee_ID]= 2;"

hth
Jeff Bridgham
bridgham@purdue.edu

 

[RJFost]

Good idea, but I was referring to the ' that's embedded inside the string literal (Carl's friend). I've had best luck with either explicitly coding it as a literal (\') or doing a run-time substitution for a constant (e.g., CONST Apos = Chr(39); "...COMMENTS = 'Carl'" & Apos & "s friend&quot

 

[jebry]

Hi RJFrost!

Yeah I understood which apostrophe you were referring to. I like to use means to allow apostrophes because, in many business environments, they are impossible to get rid of. They can be particularly annoying when they are included in field data because you can't hard code around the way you suggest(which is an idea I had never considered, I always seem to learn something on this site!) and removing them is often unacceptable. So that is why I have gotten in the habit of using the public constant trick and just not worry if an apostrophe is included or not.

Jeff Bridgham
bridgham@purdue.edu