Reading String with single quote ' from excel 1

[sgupta76]

I am trying to read a string from an excel field that has a single quote within it. For instance : "St.John's Hospital".

I pass the value into variable say 'sString'
Then I run the statement in VBA
Docmd.RunSql "Insert into Table1(Name) values('" & sString & "')

Since I am passing the string with a single quote the statement gives me a syntax error because of the single quotes. How can I read the string from excel to avoid getting the syntax error?

Thanks for your help

 

[RoyVidar]

Try either:

[tt]...values(""" & sString & """)"[/tt]

or

[tt]...values('" & replace(sString,"'","''" & "')"[/tt]

Roy-Vidar

 

[RoyVidar]

Oups - missed a closing parens in the last one:

[tt]...values('" & replace(sString,"'","''") & "')"[/tt]

Roy-Vidar

 

Thanks much. the first solution worked!!