How do you handle strings containing apostrophes in SQL?

[julietbrown]

The following SQL statement 'runs' fine unless one of the strings rstContact!Firs tNames or rstContact!Fami lyName contains an apostrophe. I can see why this causes a syntax error, but don't know how to cure it!

  
"INSERT INTO TEMP_NOT_GOT_SMI (ContactID,FirstNames,FamilyName,OrganisationID) VALUES (" & rstContact!ContactID & ",'" & rstContact!FirstNames & "','" & rstContact!FamilyName & "'," & rstContact!OrganisationID & ")"

Well, actually I DO know how to cure it: by using VBA code (.AddNew ... .Upate) on a recordset, but this is going to be a massive job throughout my project. It will, of course, have to be done if there's no way of "hiding" the apostrophes from SQL!

[MMcCarthy]

You need to replace your single quotes with double quotes. Using a double set of double quotes will tell access you want to use double quotes to enclose variable.

"INSERT INTO TEMP_NOT_GOT_SMI (ContactID,FirstNames,FamilyName,OrganisationID) VALUES (" & rstContact!ContactID & ",""" & rstContact!FirstNames & """,""" & rstContact!FamilyName & """," & rstContact!OrganisationID & ")"

[julietbrown]

That's spectacular! Thanks so much.

And there was I feeling all chuffed that I'd managed to write a dodgy little function to be called absolutely 1000s of times to 'package up' any apostrophes to hide them away from SQL. Now, thank God, I don't need it!

[MMcCarthy]

Glad to be able to help :)