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.
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
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
Here's a before and after sample using the
'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)
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)