General information
Category:
Coding, syntax & commands
Title:
Quotes, apostrophes in data
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
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only