> ## Content Index > Fetch the complete content index at: https://nolongerset.com/llms.txt > Use this file to discover other available public pages before exploring further. # Quoth thy SQL? Evermore! - URL: https://nolongerset.com/quoth-thy-sql-evermore/ - Published: 2020-09-29T15:12:23.000Z - Updated: 2026-05-08T13:08:37.000Z - Description: Solving the "O'Malley problem" with dedicated functions to sanitize our strings. Little Bobby Tables would be proud. - Author: Mike Wolfe - Tags: Code Library, #StringFunctions, #Import 2026-05-20 02:59 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: ```vba 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. ```vba 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](https://nolongerset.com/python-inspired-doc-tests-in-vba/).* ```vba '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: ```vba '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: ```vba '--== 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: ```vba 'This is OK: Where = "RptDate = #" & Me.tbRptDate & "#" 'But I find this more readable: Where = "RptDate = " & Dt(Me.tbRptDate) ``` *Image by [Alexas\_Fotos](https://pixabay.com/users/Alexas%5FFotos-686414/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=1002849) from [Pixabay](https://pixabay.com/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=1002849)*