Building String References with Embedded Quotes in Access 2007
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.
Summary: In Microsoft Office Access 2007, learn a simple way to build a WHERE clause in code that contains embedded quotes in strings. (9 printed pages)
.gif "Office Visual How To")
Applies to: 2007 Microsoft Office System, Microsoft Office Access 2007
Adapted from Access Cookbook, 2nd Edition by Ken Getz, Paul Litwin, and Andy Baron. Copyright © 2004, O'Reilly Media, Inc. All rights reserved. Used with permission.
Ken Getz, MCW Technologies, LLC
May 2009
.gif "Overview")
When you build a string that represents a WHERE clause, it can be difficult to embed a string in that string. Because you must delimit embedded strings, dates, and numbers differently, the work can quickly complicate. In addition, it is tricky to embed a quote within a quoted string. This article demonstrates a simple, reusable solution that gives you a way to create WHERE criteria that contain embedded strings, dates, and numbers. |
.gif "See It")
.jpg "Building String References with Embedded Quotes")
Length: 10:34 | Size: 10.80 MB | Type: WMV file |
Code It | Read It | Explore It
Code It
As you work in Access 2007, you face many situations in which you must provide a string expression that contains other strings. For example, when you use domain functions (DLookup, DMax, DMin, and so on), when you build a SQL expression on the fly, or when you use the Find methods on a recordset (FindFirst, FindNext, FindPrevious, and FindLast). In all of these cases, you face the same problem. Because all strings must be enclosed in quotes and because you cannot embed quotes inside a quoted string, you can quickly find yourself in trouble. Many programmers agonize over these constructs, but the situation does not need to be that difficult. This demonstration explains the problem and shows you a generic solution.
To see an example of building expressions on the fly, load and run frmQuoteTest from BuildStringReferences.accdb. Use the form to specify criteria and then click Search. The code attached to the Search button builds the SQL expression that is shown in the text box, and sets the RowSource property for the list box at the bottom of the form accordingly.
Figure 1. Test form, frmTestQuote, generates SQL expressions on the fly
.jpg "Test form generates SQL expressions on the fly")
To try each feature on the form, follow these steps:
In the First Name text box, type the letter A (see Figure 1). When you press ENTER, the form builds the appropriate SQL string and filters the list box. Note that the value you typed is enclosed by quotes in the SQL string.
In the Birth Date text box, type 3/13/60. Again, the form should filter the data (down to a single row). Note that the SQL expression must have number signs (#) around the date value that you type.
Click the Reset button to delete all the data from the four text boxes and to reset the list box back to the complete set of rows. Type the number 8 in the ID text box, and then press ENTER. Note that the SQL string this time has no delimiter around the value that you typed because it is a numerical value.
It is interesting to examine some of the code in frmQuoteTest. For example, the following code executes when you click the Search button.
Const START_SQL = "Select [Employee ID] As ID,[Last Name], " _
& "[First Name], [Birth Date], City From Employees"
Private Sub cmdSearch_Click()
Dim strSQL As String
Dim strWhere As String
' Start fresh each time.
strSQL = START_SQL
If Not IsNull(Me.txtID) Then
strWhere = BuildWhere(strWhere, "[Employee ID]", _
CLng(Me.txtID), False)
End If
If Not IsNull(Me.txtLastName) Then
strWhere = BuildWhere(strWhere, "[Last Name]", _
Me.txtLastName, Me.chkUseLike)
End If
If Not IsNull(Me.txtFirstName) Then
strWhere = BuildWhere(strWhere, "[First Name]", _
Me.txtFirstName, Me.chkUseLike)
End If
If Not IsNull(Me.txtBirthDate) Then
strWhere = BuildWhere(strWhere, "[Birth Date]", _
CVDate(Me.txtBirthDate), False)
End If
If Len(strWhere) > 0 Then strWhere = " WHERE " & strWhere
Me.txtSQL = strSQL & strWhere
Me.lstEmployee.RowSource = strSQL & strWhere
End Sub
This procedure assumes that you have only typed data into a single text box on the form. Based on the text box that you choose, the procedure constructs a WHERE clause to filter the data. (In each case, the code calls the BuildWhere function, which creates the WHERE clause.)
Private Function BuildWhere( _
strWhere As String, strFieldName As String, _
varValue As Variant, fLike As Boolean)
' A common function to build up the WHERE clause.
If Len(strWhere) > 0 Then strWhere = strWhere & " AND "
If fLike Then
strWhere = strWhere & strFieldName & _
" Like " & FixUp(varValue & "*")
Else
' Use Access' built-in BuildCriteria function
strWhere = strWhere & _
BuildCriteria(strFieldName, GetDBType(varValue), varValue)
End If
BuildWhere = strWhere
End Function
Use this function to specify a WHERE clause, a field name, a field value, and a Boolean value that indicates whether you want to use the Like syntax with a wildcard. If you choose to use the Like syntax, the BuildWhere procedure builds up the WHERE clause criterion manually, calling the FixUp function (which you'll investigate in the next section). If you request an exact match, the code can use the built-in BuildCriteria method, which formats the criterion correctly for you.
Read It
When you examined the sample form, you probably noticed that different types of data require different types of delimiters. For example, strings require quotes, dates require the number sign (#), and numbers don't require a delimiter at all. Hopefully you understand now that different data types require specific delimiters when they become part of an expression. For example, to use DLookup to find the row in which the [LastName] field was “Smith”, you need an expression that is similar to the following code.
[LastName] = "Smith"
Without the quotes, Access interprets the code as a reference to a variable named "Smith".
Date values do not require quotes. Instead, they require number sign (#) delimiters. To find the row in which the [BirthDate] field is “May 16, 1956,” use an expression similar to the following code.
[BirthDate] = #5/16/56#
Without the delimiters, Access interprets the code as a series of division problems; first, 5 divided by 16, and then that result divided by 56.
Numeric values do not require delimiters. To find the row in which the ID value is “8”, use the following expression.
[ID] = 8
Many situations in Access require you to create strings that specify search criteria. Because the Jet database engine has no information about VBA or its variables, you must supply the actual values before you apply any search criteria or perform lookups. That is, you must create a string expression that contains the value of any variable involved, not the variable name.
Any of the three examples in this section could be used as search criteria, but first, string values must be enclosed in quotes. The next few paragraphs discuss the steps that you must take when you create search-criteria strings.
To build expressions that involve variables, you must supply any required delimiters. For numeric expressions, there is no required delimiter. If the variable named intID contains the value “8”, you could use the following expression to create the search string.
"[ID] = " & intID
As part of a SQL string, or as a parameter to DLookup, this string is unambiguous in its directions to Access.
To create a search criterion that includes a date variable, you must include number sign (#) delimiters. For example, if you have a variant variable named varDate that contains the date “May 22, 1959”, and you want to end up with the following expression, you must insert the delimiters yourself.
"[BirthDate] = #5/22/59#"
The solution might look similar to the following code.
"[BirthDate] = #" & varDate & "#"
The complex case occurs when you must include strings. For those cases, you must build a string expression that contains a string itself, enclosed in quotes, with the whole expression also enclosed in quotes. To work with strings in Access, use the following rules:
An expression that is delimited with quotes cannot itself contain quotes.
Access interprets two quotes ("") inside a string as a single quote.
You can use apostrophes (') as string delimiters.
An expression that is delimited with apostrophes cannot itself contain apostrophes.
You can use the value of Chr$(34) (34 is the ANSI value for the quote character) inside a string expression to represent the quote character.
Given these rules, you can create a number of solutions to the same problem. For example, if the variable strLastName contains "Smith", and you want to create a WHERE clause that will search for that name, you might end up with the following expression.
"[LastName] = "Smith""
However, that expression is not allowed because it includes internal quotes. The following code is an acceptable solution.
"[LastName] = ""Smith"""
The problem is that the literal value "Smith" is still in the expression. If you try to replace that value with the name of the variable, strLastName, you might write the following expression.
"[LastName] = ""strLastName"""
The result is a search for a row with the last name of "strLastName". You probably won't find a match.
One solution, then, is to break up that expression into three separate pieces—the portion before the variable, the variable itself, and the portion after the variable (the final quote).
"[LastName] = """ & strLastName & """"
Although that may look confusing, it is correct. The first portion is simply a string that contains the name of the field, an equals sign, and two quotes.
"[LastName] = """
The rule is that two quotes inside a string are treated as one. The same logic works for the portion of the expression after the variable (""""). That is a string that contains two quotes, which Access interprets as one quote. Although this solution works, it is a bit confusing.
To make things simpler, you can just use apostrophes inside the string.
"[LastName] = '" & strLastName & "'"
This is somewhat less confusing, but there is a serious drawback. If the name itself contains an apostrophe ("O'Connor", for example), the code will not work because an expression that is delimited with apostrophes cannot itself contain apostrophes. This solution works only when you're assured that the data in the variable can never itself include an apostrophe.
The simplest solution is to use Chr$(34) to embed the quotes; for example, you might use the expression in the following code.
"[LastName] = " & Chr$(34) & strLastName & Chr$(34)
If you don't believe this works, go to the Immediate window in VBA and type the following code.
? Chr$(34)
Access returns the value of Chr$(34) — a quote character.
To make this solution a little simpler, you could create a string variable at the beginning of your procedure and assign to it the value of Chr$(34).
Dim strQuote As String
Dim strLookup As String
strQuote = Chr$(34)
strLookup = "[LastName] = " & strQuote & strLastName & strQuote
This actually makes the code almost readable!
Finally, if you do not want to define that variable in every procedure that you write, you might try using a constant such as the following code instead.
Const QUOTE = Chr$(34)
Unfortunately, you cannot create a constant in Access whose value is an expression. If you want to use a constant, you must use the "two-quote" rule in the following code.
Const QUOTE = """"
Although the utility of this expression is not immediately clear, it works just fine. The constant is two quotes (which Access interprets as a single quote) inside a quoted string. Using this constant, the previous expression becomes the following code.
strLookup = "[LastName] = " & QUOTE & strLastName & QUOTE
To encapsulate all of these rules, you might want to use the FixUp function in the basFixUpValue module in BuildStringReferences.accdb. This function takes a variant value as a parameter and encloses it in the appropriate delimiters. The following code is the source code for the function.
Public Function FixUp(ByVal varValue As Variant) As Variant
' Add the appropriate delimiters, depending on the data type.
' Put quotes around text, "#" around dates, and nothing
' around numeric values.
'
' If you're using equality in your expression, you should
' use Basic's BuildCriteria function instead of calling
' this function.
Const QUOTE = """"
Select Case VarType(varValue)
Case vbInteger, vbSingle, vbDouble, vbLong, vbCurrency
FixUp = CStr(varValue)
Case vbString
FixUp = QUOTE & varValue & QUOTE
Case vbDate
FixUp = "#" & varValue & "#"
Case Else
FixUp = Null
End Select
End Function
Once you include this function in your own application, you can call it to format the data for you. The sample code in frmQuoteTest uses this function. For example, the following code illustrates how to build the expression from the previous example.
"[LastName] = " & FixUp(strLastName)
FixUp figures out the data type and encloses the data with the necessary delimiters.
Access also provides a useful function, BuildCriteria, that accepts a field name, a data type, and a field value, and then creates an expression similar to the following.
FieldName = "FieldValue"
The expression contains the appropriate delimiters, depending on the data type. This example uses this technique in the case where you uncheck the Use Like checkbox. It won't help if you want an expression that uses wildcards, but if you're looking for an exact match, it inserts most of the correct delimiters for you. To study the example, look at the BuildWhere function in the frmQuoteTest module.