Quoth thy SQL? Evermore!

I write a lot of dynamic SQL in Access.  Even if you only use predefined query objects, you still need to be able to generate WHERE clauses in code.  For text fields, this means you need to quote your strings.  More importantly, it means you need to escape your strings, too.

Strings in VBA are enclosed in double-quotation marks (").  If you want to quote an inner string, you escape the double-quotes by doubling them up.  For example, "George Herman ""Babe"" Ruth, Jr.".  Single-quotes do not require such escaping, so this is valid, "Dwayne 'The Rock' Johnson".  The Access query engines (Jet/ACE) allow either double- or single-quotes.  This allows you to create a where clause in VBA like this, Where = "FirstName = 'Lenore'".

For example, let's say I have a lookup form and I want users to be able to search on the last name field.  I might be tempted to use the following approach:

Where = "LastName Like '*" & Me.tbLastName & "*'"

If a user enters "Jo" as a partial last name, our Where string will contain LastName Like '*Jo*' and match both "Johnson" and "Jones".  So far, so good.

Until one day a user wants to search for "O'Malley."  Now our Where string contains LastName Like '*O'Malley'.  The problem is that the apostrophe in "O'Malley" is now acting as the closing quote in our query clause.  The query engine complains about invalid syntax because Malley' is not a keyword with which it is familiar.

We can avoid this by replacing the single quote with an escaped double quote.

Where = "LastName Like ""*" & Me.tbLastName & "*"""

The problem is that the above line of code is less readable than our earlier version.  Trying to parse out what "*""" means in our head is not straightforward.  Plus, we still haven't eliminated the "O'Malley" problem.  As soon as a user searches for O"Malley, we're back to our invalid syntax.  And before you say, "Users shouldn't be using double-quotes when entering people's names," let me say, "They will."  It's your job as a programmer to sanitize your users' inputs.

My solution

I wouldn't be writing this article if I didn't have a solution, right?  I created the following two functions to wrap strings in double and single quotes, respectively.  I intentionally chose very short names for these functions because I didn't want to distract from the text they were enclosing.

I also wanted to make it easier to write UPDATE and INSERT queries using these functions.  For that reason, if you pass a Null value to any of these functions, they will return the unquoted string literal Null.  This allows you to do things like "SET LastName = " & Qt(Me.tbLastName) and have it work whether tbLastName is Null or a string.  Take note that this does not apply to WHERE clauses because you need to use the Is Null SQL statement when testing for a null field (and not = Null).

The Good Stuff

NOTE: The comment lines that begin with '>>> represent doc tests.

'SQL String building functions vv
'   If passed a Null value these functions will return the text 'Null'
'   this allows the functions to be used in SQL text without additional special handling
'>>> Qt("Dwayne ""The Rock"" Johnson")
'    "Dwayne ""The Rock"" Johnson"
'>>> "SET LastName = " & Qt(Null)
'    SET LastName = Null
'>>> "_" & Null & "_"
'    __
'>>> "_" & Qt(Null) & "_"
'    _Null_
'>>> "_" & Qt("Null") & "_"
'    _"Null"_
Function Qt(Text As Variant) As String
Const QtMark As String = """"
    'We gain a small performance boost by declaring the quotation marks as a constant because they
    '  get resolved at compile time rather than run time
    'We also replace double quotes in the string with escaped double quotes so that data
    '  containing double quotes do not blow up our SQL statements
    If IsNull(Text) Or IsEmpty(Text) Then
        Qt = "Null"
    Else
        Dim s As String
        s = Text
        Qt = QtMark & Replace(s, QtMark, QtMark & QtMark) & QtMark
    End If
End Function


'>>> Qs("Dwayne 'The Rock' Johnson")
'    'Dwayne ''The Rock'' Johnson'
'>>> "_" & Null & "_"
'    __
'>>> "_" & Qs(Null) & "_"
'    _Null_
'>>> "_" & Qs("Null") & "_"
'    _'Null'_
Function Qs(Text As Variant) As String
Const QtSingle As String = "'"
    'We replace single quotes in the string with escaped single quotes so that data
    '  containing double quotes do not blow up our T-SQL statements
    If IsNull(Text) Or IsEmpty(Text) Then
        Qs = "Null"
    Else
        Dim s As String
        s = Text
        Qs = QtSingle & Replace(s, QtSingle, QtSingle & QtSingle) & QtSingle
    End If
End Function


'>>> Dt(DateSerial(2018, 2, 4))
' #2/4/2018#
'>>> Dt("Gibberish")
' #Gibberish#
'>>> IsNull(Dt(Null))
' False
'>>> "_" & Dt(Null) & "_"
' _Null_
'>>> Dt("Null")
' #Null#
Function Dt(DateVal As Variant) As String
Const Pound As String = "#"
    If IsNull(DateVal) Or IsEmpty(DateVal) Then
        Dt = "Null"
    Else
        Dt = Pound & DateVal & Pound
    End If
End Function

Usage

Here's a before and after sample using the Qt() function:

'BAD, leads to O'Malley errors
Where = "LastName = '" & Me.tbLastName & "'"   

'GOOD, more readable and properly escaped
Where = "LastName = " & Qt(Me.tbLastName)      

Here's an example demonstrating the extra bit of Null handling I included:

'--== BEFORE ==--
If IsNull(Me.tbLastName) Then
    SetClause = "LastName = Null"
Else
    SetClause = "LastName = '" & Me.tbLastName & "'"
End If


'--== AFTER ==--
SetClause = "LastName = " & Qt(Me.tbLastName)

Dates, too

Finally, since dates in Access need to be enclosed in pound signs (#), I created a Dt() function to perform the same duty for date fields.  I find it more readable than using "#" directly, especially since it matches my Qt() function in terms of naming convention.  Also, I don't have to worry about forgetting a # when I'm writing my code:

'This is OK:
Where = "RptDate = #" & Me.tbRptDate & "#"

'But I find this more readable:
Where = "RptDate = " & Dt(Me.tbRptDate)   

Image by Alexas_Fotos from Pixabay