Curly Quotes in MS Access

If you cut and paste text from MS Word, or some other writing application, into MS Access curly quotes, sometimes called smart quotes can be a problem. When you try to run that text through VBA or SQL the quotes can choke your code, especially the single quotes or apostrophes. I have a function to convert single quotes to two single quotes before handling the text in VBA, but it is for straight quotes, Chr(39), and does not recognize the curly quote. To fix this problem I wrote a new function that converts the curly quotes to straight quotes when the text is saved in Access. If you are not using unbound text boxes, you will have to call the function at some other point.

Here is the function:

Public Function fnCurlyQuotes(strText As String)

     'Convert a left single curly quote to a straight single quote
     strText = Replace(strText, Chr(145), Chr(39))

     'Convert a right single curly quote to a straight single quote.

     strText = Replace(strText, Chr(146), Chr(39))

     'Convert a left double curly quote to a straight double quote.

     strText = Replace(strText, Chr(147), Chr(34))

     'Convert a right double curly quote to a straight double quote.

     strText = Replace(strText, Chr(148), Chr(34))

fnCurlyQuotes = strText

End Function

To use the function I simply include it in the On Click event of my form’s Save button:

Private Sub cmdSave_Click()

     Dim intIndex As Integer

     Dim strText As String

     Dim strSQL As String

     intIndex = Me.txtIndex

     'Debug.Print "intIndex = " & intIndex

     strText = Nz(Me. strText, "")

     'Debug.Print "strText = " & strText

     'Convert all double and single curly quotes to straight quotes.

     strText= fnCurlyQuotes(strText)

     'Convert all single quotes to two single quotes.

     strText= fnSingleQuote(strText)

     strSQL = "INSERT INTO tblTable ([Index], [Text]) “

     strSQL = StrSQL & “VALUES (" & intIndex & ", '" & strText & "')"

    

     DoCmd.RunSQL strSQL

End Sub

SQL failing in VBA due to apostrophes

The most common reason an SQL statement fails for when I try to run it via VBA, using DoCmd.RunSQL, is that I forget to account of apostrophes or single quotes. This is easly fixed by using the replace() function.

'Pick up the value for your variable.

strSearchTerm = Me.txtSearchBox

'Then run the variable through the replace() function, 

'replacing each single quote with two single quotes.

'This tells the parser to ignore the single quote.

strSearchTerm = Replace(strSearchTerm, " ' ", " ' ' ")

'You can also do this using the ASCII designation for a single quote, chr(39).

strSearchTerm = Replace(strSearchTerm, Chr(39), Chr(39) & Chr(39))