I'm trying to construct SQL commands as a string to use in MS Access modules. This works fine except for when I want to insert data that contains double quotes and/or single quotes (apostrophes). I know that doubling up on the quotes is the answer but I want to create a routine that I can pass the string to and it will add the duplicate quotes where necessary. I created the following test routines:

Public Sub q()
Dim x As String, ssql As String
x = quoteIt("This is a test of Sal's ""so-called work""")
ssql = "Insert into tblorder_notes (ifscustno, orderno, lineno, notefld) " _
& " values ('SJG', '99999991', 0, '" & x & "')"
DoCmd.RunSQL ssql
End Sub


Public Function quoteIt(sStr)
Dim sQuote As String, sDQuote As String
sDQuote = Chr$(34)
sQuote = Chr$(39)
quoteIt = "replace(replace(sStr, " & sDQuote & sDQuote & sDQuote & sDQuote & ", " _
& sDQuote & sDQuote & sDQuote & sDQuote & sDQuote & sDQuote & "), " _
& sDQuote & sQuote & sDQuote & ", " & sDQuote & sQuote & sQuote & sDQuote & ")"
End Function

All it should do is insert the following notefld data:

This is a test of Sal's "so-called" work

Can't get it to work. I'm sure I have too many or not enough quotes or quotes of the wrong type. I'm going crazy! I would love any help!

TIA,

Sally