|[ Team LiB ]|
Recipe 7.1 Build Up String References with Embedded Quotes
You'll face this problem in any place in Access where you're required to provide a string expression that contains other strings—for example, in using the domain functions (DLookup, DMax, DMin, etc.), in building a SQL expression on the fly, or in using the Find methods (FindFirst, FindNext, FindPrevious, and FindLast) on a recordset. Because all strings must be surrounded with quotes, and you can't embed quotes inside a quoted string, you can quickly find yourself in trouble. Many programmers agonize over these constructs, but the situation needn't be that difficult. This section explains the problem and shows you a generic solution.
To see an example of building expressions on the fly, load and run frmQuoteTest from 07-01.MDB. This form, shown in Figure 7-1, allows you to specify criteria. Once you press the Search button, the code attached to the button will build the SQL expression shown in the text box and will set the RowSource property for the list box at the bottom of the form accordingly.
To try all the features of the form, follow these three steps:
The point of that exercise was to alert you to the fact 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'd need an expression like this:
[LastName] = "Smith"
Leaving off those quotes would confuse Access, because it would be looking for some variable named "Smith".
[BirthDate] = #5/16/56#
If you left off the delimiters, Access would think you were trying to numerically divide 5 by 16, and then by 56.
Numeric values require no delimiters. If you were searching for the row in which the ID value was 8, you could use this expression:
[ID] = 8
and Access would know exactly what you meant.
Many situations in Access require that you create strings that supply search criteria. Because the Jet database engine has no knowledge of 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 have been used as search criteria, and string values would need to have been surrounded by quotes. The next few paragraphs cover the steps you need to take in creating these 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 this expression to create the search string you need:
"[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'll need to include the # 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 this expression:
"[BirthDate] = #5/22/59#"
you have to insert the delimiters yourself. The solution might look like this:
"[BirthDate] = #" & varDate & "#"
The complex case occurs when you must include strings. For those cases, you'll need to build a string expression that contains a string itself, surrounded by quotes, with the whole expression also surrounded by quotes. The rules for working with strings in Access are as follows:
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 will end up with this expression:
"[LastName] = "Smith""
However, that expression isn't allowed because it includes internal quotes. An acceptable solution would be the following:
"[LastName] = ""Smith"""
The problem here is that the literal value "Smith" is still in the expression. You're trying to replace that value with the name of the variable, strLastName. You might try this expression:
"[LastName] = ""strLastName"""
but that will 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, and the portion after the variable (the final quote):
"[LastName] = """ & strLastName & """"
Although that may look confusing, it's correct. The first portion:
"[LastName] = """
is simply a string containing the name of the field, an equals sign, and two quotes. 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's a string containing two quotes, which Access sees as one quote. Although this solution works, it's a bit confusing.
"[LastName] = '" & strLastName & "'"
This is somewhat less confusing, but there's a serious drawback: if the name itself contains an apostrophe ("O'Connor", for example), you'll be in trouble. Access doesn't allow you to nest apostrophes inside apostrophe delimiters, either. This solution works only when you're assured that the data in the variable can never itself include an apostrophe.
"[LastName] = " & Chr$(34) & strLastName & Chr$(34)
If you don't believe this works, go to the Immediate window in VBA and type this:
Access will return to you by typing 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!
Const QUOTE = Chr$(34)
Unfortunately, Access won't allow you to create a constant whose value is an expression. If you want to use a constant, your answer is to rely on the "two-quote" rule:
Const QUOTE = """"
Although this expression's use is not immediately clear, it works just fine. The constant is two quotes (which Access will see as a single quote) inside a quoted string. Using this constant, the previous expression becomes:
strLookup = "[LastName] = " & QUOTE & strLastName & QUOTE
To encapsulate all these rules, you might want to use the acbFixUp function in the basFixUpValue module in 07-01.MDB. This function takes as a parameter a variant value and surrounds it with the appropriate delimiters. Its source code is:
Function acbFixUp(ByVal varValue As Variant) As Variant ' Add the appropriate delimiters, depending on the data type. ' Put quotes around text, #s 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 acbFixUp = CStr(varValue) Case vbString acbFixUp = QUOTE & varValue & QUOTE Case vbDate acbFixUp = "#" & varValue & "#" Case Else acbFixUp = Null End Select End Function
Once you've included this function in your own application, you can call it, rather than formatting the data yourself. The sample code in frmQuoteTest uses this function. For example, here's how to build the expression from the previous example:
"[LastName] = " & FixUp(strLastName)
|[ Team LiB ]|