Append employee name with '

[jgelpi16]

I have an employee name with a ' in it (i.e. O'Neal). I know I have done this before but it has been a while and I cannot remember how to do it for the life of me. I'm executing the query in VBA using the mySQL as a string method. Can someone help me out with the syntax on this? OR if you have a webpage that explains this I would greatly appreciate that as well. Thanks in advance.

[jgelpi16]

Tried this....didn't work.

Dim mySQL, strQuote as String
strQuote = """"

mySQL = "...'" & strQuote & vFULL_NAME & strQuote & "'...;"

[weekend00]

"update allfieldtype set ftext='O''Neal'" (two ' between O and Neal)

or
abc="O''Neal" (two ' between O and Neal)

currentdb.Execute "update allfieldtype set ftext='" &abc & "'"

[jgelpi16]

Now I really have the solution....

Replace the ' with '' (two single quotes, NOT a double quote)

variableName = Replace(String, "'", "''")

[jgelpi16]

Didn't see your post before mine weekend00. Thanks for the input!

[weekend00]

Glad to see you work it out.

[jgelpi16]

Can you explain how the replace function works? I'm replacing O'Neal with O''Neal so the variable value is in fact O''Neal. However, when I run the SQL and append the record into the table it appends as O'Neal. Why?

[weekend00]

the replace function works as you expected, it replace O'Neal to O''Neal,
when the SQL runs, it look on '' as ',("" will be look on as " too) while a single ' (or ") is part of the syntax, not a part of the fields's content. That's why it will cause SYNTAX ERROR when you use single ' in you text.

to understand better, please think about these:

statements:
abc="O''Neal"
currentdb.Execute "update allfieldtype set ftext='" &abc & "'"

after the statements run, ftext is set to O'Neal,

T-chart for abc and ftext:

abc                 ftext
abc="O''Neal"     O'Neal
abc="O""Neal"     O"Neal
abc="O""''Neal"     O"'Neal
abc=""""""             ""
abc="''''"              ''

[jgelpi16]

Ok, makes more sense. Thank you.