Quotes and Apostrophies in SQL Statements

[craigachan]

I'm having problems with quotation marks in a sql statement. I can't seem to figure out what my problems is. The string is an array separated by a semicolon.

120/80;70;5'6";125

this string represents patient vitals. I'm using the string to update a record. But I get hung up with the quotation mark.

I've tried: 120/80;70;5''6'"';125 which is a enclosing the quotation mark with apostrophies, but this does not seem to work. The sql still gets hung up. My sql statment looks something like:

original string: 120/80;70;5'6";125

strPreOpVits = "120/80;70;5''6'"';125"

mysql = "UPDATE mytable SET PreOpVits = '" & strPreOpVits & "' " & _
"WHERE nID = " & myRecID

docmd.runsql mysql

I've narrowed it down to the quotation marks and I'm unsure how to handle these. I get a runtime 3075 - Syntax Error. Can anyone explain what I'm doing wrong?

Here is the code that I use to convert the original string

Public Function FixQuotesInSql(strToFix As String)
    Dim lgth, y  As Long
    Dim strTemp, char2Add As Variant
    'This routine fixes the use of apostrophy and quotation marks in an SQL sequence
    'If the apostrophy is at the beginning or end of the string it replaces with 3 x "'" or "'''"
    'If in the middle of the string then replaces with 2 x "'" or "''"
    'If a quotation marks occurs " then it is surrounded by '"'.  Ex: My height is 5' 9" is then My height is 5''9 '"'.
    lgth = Len(strToFix)
    strTemp = Null
    
    'Need to add each character or character replacement one at at time.
    For y = 1 To lgth
        If Mid(strToFix, y, 1) = Chr(39) Then
            Select Case y
                Case 1
                    char2Add = Chr(39) & Chr(39) & Chr(39)
                Case 2 To (lgth - 1)
                    char2Add = "''"
                Case lgth
                    char2Add = Chr(39) & Chr(39) & Chr(39)
            End Select
        Else
            If Mid(strToFix, y, 1) = Chr(34) Then
                char2Add = Chr(39) & Chr(34) & Chr(39)
            Else
                char2Add = Mid(strToFix, y, 1)
            End If
        End If
        
        strTemp = strTemp & char2Add
    Next y

    FixQuotesInSql = strTemp
End Function

 

[Galaxiom]

Escape them by doubling them.

 

[craigachan]

Thank for helping me out. I'm somewhat of a novice VBAer, can you explain to me what doubling them means?

 

[Galaxiom]

Because the singe quote and double quote characters are used as string delimiters there needs to be a way to indicate that the character is meant to be interpreted literally rather than as a delimiter.

This process is generally known as "escaping" because it escapes the usual meaning as a delimiter. In many places in Access and VBA the literal escape is the forward slash.

However in the case quotes and double quotes in VBA the escape character is itself. So to enter a quote when you really mean you want a quote character then you put two in a row. Same with a double quote.

However you only need to double them if they could potentially be interpreted as a delimiter in the context where they appear. If you open a string with a double quote then the next double quote needs to be doubled unless it closes the string. Similarly with the single quote.

It can be tricky to get it right. Consequently some developers prefer to keep the double quote marks as delimiters and enter others with the Chr() function.

Chr(34) is a double quote.

If you Google that expression and VBA you will find many examples.

 

[smig]

Using chr(34) will make your life simpler

 

[craigachan]

Thank you for your insight. I thought I was doing that in my code to get the converted version of my string. But Now I've changed my code from:

            If Mid(strToFix, y, 1) = Chr(34) Then
                [COLOR="Red"][B]char2Add = Chr(39) & Chr(34) & Chr(39)[/B][/COLOR]  
            Else
                char2Add = Mid(strToFix, y, 1)
            End If

TO:

            If Mid(strToFix, y, 1) = Chr(34) Then
                [COLOR="red"][B]char2Add = "Chr(34)" [/B][/COLOR]                        
            Else
                char2Add = Mid(strToFix, y, 1)
            End If

But it save my string as: 120/80;45;5'6Chr(34);125, literally.

When I tried: char2Add = Chr(34) & Chr(34) & Chr(34)

I got: 120/80;45;5'6""";125

By the way, if I remove the quotation mark from the string, the code updates successfully, so I'm pretty sure I'm working with a problem with the double quotes.

Can you tell me what is wrong with my code?

 

[craigachan]

Okay, I've tried just using double quotation marks like:

char2Add = Chr(34) & Chr(34)

The UPDATE will then run But then I just get: 120/80;45;5'6"";125

I don't want to save my string like this as it will throw off other things.

ADDITIONALLY - I want to add escaping for "%". using the following code:

If Mid(strToFix, y, 1) = Chr(37) Then
char2Add = Chr(34) & Chr(37) & Chr(34)
end if

UPDATE goes good with this But then again I get the string saved as:

2"%"Lidocaine with epinephrine

I don't want to save these strings with the escapes.
Anyone have any ideas? Would greatly be appreciated.

 

[craigachan]

OKAY here is the code that works for me. There were may examples that I found online but non of them worked for me. This is used to setup the SET portion of and UPDATE sql statement. Not sure why the other examples didnt work.

Public Function FixEscapesInSql(strToFix As String)
    Dim lgth, y  As Long
    Dim strTemp, char2Add As Variant
    'This routine fixes the use of apostrophy, quotation marks, and percent in an SQL sequence

    lgth = Len(strToFix)
    strTemp = Null
    
    'Need to add each character or character replacement one at at time.
    For y = 1 To lgth
        If Mid(strToFix, y, 1) = Chr(39) Then              'Fix the single quotes chr(39)
            Select Case y
                Case 1
                    char2Add = "'&'" & Chr(39) & Chr(39) & "'&'"
                Case 2 To (lgth - 1)
                    char2Add = Chr(39) & Chr(39)
                Case lgth
                    char2Add = "'&'" & Chr(39) & Chr(39) & "'&'"
            End Select
        ElseIf Mid(strToFix, y, 1) = Chr(34) Then        'Fix the double quotes Chr(34)
            Select Case y
                Case 1
                    char2Add = "'&'" & Chr(34) & "'&'"
                Case 2 To (lgth - 1)
                    char2Add = "'&'" & Chr(34) & "'&'"
                Case lgth
                    char2Add = "'&'" & Chr(34) & "'&'"
            End Select
        ElseIf Mid(strToFix, y, 1) = Chr(37) Then        'Fix the percents Chr(37)
                char2Add = "'&'%'&'"
        Else
                char2Add = Mid(strToFix, y, 1)
        End If
        
        strTemp = strTemp & char2Add
    Next y
    
'Prevent adding extra quotes on subsequent saves.
doagain:
    If InStr(1, strTemp, Chr(34) & Chr(34) & Chr(34)) > 1 Then
        strTemp = replace(strTemp, Chr(34) & Chr(34) & Chr(34), Chr(34) & Chr(34))      'replace """ with ""
        GoTo doagain
    End If
    
    FixEscapesInSql = strTemp
End Function