This month, Doug Steele shows one way to add shortcuts to your application, demonstrates how to handle embedded quotes in SQL statements, and provides a quick documenter to list the objects in your application.
See the rest of the Access Answers article on : Shortcuts, and What Have I Done Here
I’m building SQL strings in code, but I’m having problems handling text that includes quotation marks.
When you’re building your SQL strings dynamically, incorporating variables in the string can sometimes be problematic. Remember that what you need to do is enclose, in quotes, any text values in the SQL string. In other words, you need your SQL string to look something like this:
...WHERE [Comment] = "Smith"
or:
...WHERE [Comment] = 'Smith'
To use a variable rather than the constant, you need to explicitly add the quote marks, and concatenate the value of the variable (instead of the variable itself) into your SQL string. In other words, you need to write code like this, which puts double quotes around the value:
strSQL = strSQL & "WHERE [Comment] = """ & strText & """"
You can also write code like this that puts single quotes around the value:
strSQL = strSQL & "WHERE [Comment] = '" & strText & "'"
It may be hard to make out the last part of the code: After the last ampersand, I have a double quote-single quote-double quote combination.
It’s also possible to create a constant cQuote and set it to “”””, so that you can use:
strSQL = strSQL & "WHERE [Comment] = " & cQuote & strText & cQuote
This eliminates some of the ugliness of the first example. Since Chr$(34) is the same as “, you can also use:
strSQL = strSQL & "WHERE [Comment] = " & Chr$(34) & strText & Chr$(34)
The problem with any of these approaches is that they can fail to work if strText contains quotes. For example, if you use the second approach, it’s going to fail if strText contains a single quote in it (such as O’Brien). Any of the other three approaches will work with O’Brien, but will fail if you’ve got a double quote in your string (such as The “Olde Sodde”). In other words, this will work:
strText = "O'Brien" strSQL = strSQL & "WHERE [Comment] = """ & strText & """" On the other hand, this will not: strText = "O'Brien" strSQL = strSQL & "WHERE [Comment] = '" & strText & "'" Similarly, this will work: strText = "The ""Olde Sodde""" strSQL = strSQL & "WHERE [Comment] = """ & strText & """" And this will not: strText = "The ""Olde Sodde""" strSQL = strSQL & "WHERE [Comment] = '" & strText & "'"
It also depends on what database you’re talking to. If you’re writing SQL to go against SQL Server, for example, often you must use single quotes as a delimiter.
Since you don’t always know what your users are going to be keying in for search purposes, you need something a little more flexible than just concatenating single or double quotes. What I find easiest to do is to create a function that corrects the quotes for you. With such a function, rather than having to worry about counting quotes, you can simply do something like this:
strSQL = strSQL & "WHERE [Comment] = " & CorrectText(strText)
The “trick” is to ensure that whatever character you’re using to delimit your text strings (either ‘ or “), you must double any occurrence of that character within your string. A function such as the following will suffice:
Function CorrectText( InputText As String, _ Optional Delimiter As String = "'") As String Dim strTemp As String strTemp = Delimiter strTemp = strTemp & Replace(InputText, Delimiter, Delimiter & Delimiter) strTemp = strTemp & Delimiter CorrectText = strTemp End Function
The Replace function in the preceding code only works in Access 2000 and higher. If you’re using Access 97 or earlier, you’ll need to write your own equivalent function. The database in the accompanying Download includes such a function.
It’s easy to extend such a function to allow for its use with the Like function as well:
Function CorrectLikeText( InputText As String, _ Optional FrontWildcard As Boolean = False, _ Optional EndWildcard As Boolean = False, - Optional Delimiter As String = "'") As String Dim strTemp As String strTemp = Delimiter If FrontWildcard = True Then strTemp = strTemp & "*" End If strTemp = strTemp & _ Replace(InputText, Delimiter, Delimiter & Delimiter) If EndWildcard = True Then strTemp = strTemp & "*" End If strTemp = strTemp & Delimiter CorrectText = strTemp End Function
So what happens if strText contains O’Brien? You’d use my function like this to find any record whose Comment field ends in O’Brien:
strSQL = strSQL & "WHERE [Comment] LIKE " & CorrectLikeText(strText, True)
If you want to find those records with O’Brien at the start of the Comment field, use this:
strSQL = strSQL & "WHERE [Comment] LIKE " & CorrectLikeText(strText, False, True)
Finally, if you want to find O’Brien anywhere in the Comment field, call my function like this:
strSQL = strSQL & "WHERE [Comment] LIKE " & CorrectLikeText(strText, True, True)
If you’re going to be going against a DBMS other than Jet, or if you’re using ADO, you’ll need to rewrite my function to use % instead of * as the wildcard. Enhancing the function to accept the wildcard character as a parameter is another way of accommodating this:
Function CorrectLikeText( _ InputText As String, _ Optional FrontWildcard As Boolean = False, _ Optional EndWildcard As Boolean = False, - Optional Wildcard As String = "*", _ Optional Delimiter As String = "'") As String Dim strTemp As String strTemp = Delimiter If FrontWildcard = True Then strTemp = strTemp & Wildcard End If strTemp = strTemp & _ Replace(InputText, Delimiter, Delimiter & Delimiter) If EndWildcard = True Then strTemp = strTemp & Wildcard End If strTemp = strTemp & Delimiter CorrectText = strTemp End Function
Here’s my new version of the function in action:
strSQL = strSQL & "WHERE [Comment] LIKE " & _ CorrectLikeText(strText, True, True, "*") strSQL = strSQL & "WHERE [Comment] LIKE " & _ CorrectLikeText(strText, True, True, "%")
With this version of the function, you must specify True or False for both FrontWildcard and EndWildcard in order for this to work. This will succeed:
strSQL = strSQL & "WHERE [Comment] LIKE " & _ CorrectLikeText(strText, True, False, "*")
This will not:
strSQL = strSQL & "WHERE [Comment] LIKE " & _ CorrectLikeText(strText, True, , "*")
