Remove Apostrophe

[Guest]

Hi
There is a apostrophe within a text and in order to insert the text into a
table, the apostrophe must be replaced with other charaters such as doulble
quotes. how a quote is escaped as a quote ?

Clara

 

[Douglas J. Steele]

Actually, assuming you're trying to use SQL to insert the value, the
apostrophe doesn't have to be replaced with other characters. It all depends
on what you're using as the delimiter.

If you're using a double quote as the delimiter, you shouldn't have any
problem:

strSQL = "INSERT INTO MyTable (MyField) " & _
"VALUES (""" & Me.MyTextBoxWithApostrophe & """)"

or

strSQL = "INSERT INTO MyTable (MyField) " & _
"VALUES (" & Chr$(34) & Me.MyTextBoxWithApostrophe & Chr$(34) & ")"

If you're using a single quote as the delimiter, you need to double the
apostrophes:

strSQL = "INSERT INTO MyTable (MyField) " & _
"VALUES ('" & Replace(Me.MyTextBoxWithApostrophe, "'", "''") & "')"

Exagerated for clarity, that second line is

"VALUES ( ' " & Replace(Me.MyTextBoxWithApostrophe, " ' ", " ' ' ") & " ' )
"

 

[Guest]

thank you, my code is the following:
var = iif(isnull(rs("field")), "new text", replace( rs("field"), "'", "''" ))
VBA always do calculation with two rs("field") at the same time, so when
rs("field") is null, replace() return a run time error. Could you tell me the
reason.

Clara

 

[Douglas J. Steele]

That's just how VBA works.

Try:

If isnull(rs("field")) Then
var = "new text"
Else
replace( rs("field"), "'", "''" )
End If