I mentioned the FixQuotes function in the last blog, but it deserves a bit more focused attention. The issue of quotes in text strings that ultimately get passed as variables to query strings is one that has caused much consternation in my experience. Since single quotes and double quotes can both appear in code for various reasons, the FixQuotes() function will get TWO doses of attention.
SINGLE QUOTES IN TEXT STRINGS
For this first example, suppose you have a bit of code that constructs a WHERE clause based on a customer's last name:
strSQL = "SELECT * FROM tblCustomers WHERE LName = '" & me.txtLName & "'"
This snippet will work swimmingly as long as the customer's name isn't something like "O'Neil". In such a case, the resultant SQL code that gets generated will have a critical flaw that will cause a syntax error:
SELECT * FROM tblCustomers WHERE LName = 'O' Neil'
The statement now tells the query engine to look for someone who's last name is "O" and then perform the "Neil" command. Oh, and then open another quote, but don't close it.
The query engine responds with, "Wha...??" or some similar "I can't do that, Dave" -type response.
The FixQuotes() function, used in conjunction with the Quote() function, attempts to mitigate such issues. First, let's talk about the FixQuotes() function.
Function FixQuotes(strToFix As Variant, Optional DoubleQuote As Boolean = False) As String
If DoubleQuote Then
FixQuotes = Replace(Nz(strToFix), """", "'")
FixQuotes = Replace(Nz(strToFix), "'", "''")
The FixQuotes() function simply escapes the single quote using two single quotes. SQL sees the pair of single quotes (NOT the same as a single double-quote!!) as an actual single quote instead of the termination of a quote string. We will address the optional DoubleQuotes argument in the next segment.
Function Quote(StringToQuote As Variant, Optional DoubleQuotes As Boolean = False) As String
Quote = "'" & FixQuotes(StringToQuote, DoubleQuotes) & "'"
The Quote() function is just a handy programming keystroke saver that incorporates the FixQuotes() function to make it easier to wrap strings in quotes. Thus:
FixQuotes("O'Neil") returns O''Neil and Quote("O'Neil") returns 'O''Neil'
For example, the following snippets show the graduating simplification of the code:
x = "O'Neil"
This one returns an error
strSQL = "SELECT * FROM tblCustomers WHERE LName = '" & x & "'"
This one is OK, but a bit cumbersome:
strSQL = "SELECT * FROM tblCustomers WHERE LName = '" & FixQuotes(x) & "'"
Quote() wraps the string variable in quotes, fixing any quotes in the string
strSQL = "SELECT * FROM tblCustomers WHERE LName = " & Quote(x)
Adding these two functioins to your utility module will save you from SQL errors caused by spurrious quotes in your text strings plus simplify and streamline your code. Look for Part 2 where we'll get into the double quote replacement aspect of these functions.