Recipe 7.1 Build Up String References with Embedded Quotes

7.1.1 Problem

You want to create criteria for text and data fields, but no matter what syntax you try you seem to get errors or incorrect results. What are you doing wrong?

7.1.2 Solution

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.

Figure 7-1. The test form, frmQuoteTest, with a subset of the data selected

To try all the features of the form, follow these three steps:

7.1.3 Discussion

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".

Date values don't require quotes. Instead, they require # delimiters. To find the row in which the [BirthDate] field is May 16, 1956, you'd need an expression like this:

[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:

• An expression that's delimited with quotes can't itself contain quotes.

• Two quotes ("") inside a string are seen by Access as a single quote.

• You can use apostrophes (') as string delimiters.

• An expression that's delimited with apostrophes can't 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 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.

To make things simpler, you can just use apostrophes inside the string:

"[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.

The simplest solution is to use Chr$(34) to embed the quotes. An expression such as the following would do the trick:

"[LastName] = " & Chr$(34) & strLastName & Chr$(34)

If you don't believe this works, go to the Immediate window in VBA and type this:

? Chr$(34)

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!

Finally, if you grow weary of defining that variable in every procedure you write, you might consider using a constant instead. You might be tempted to try this:

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)

abcFixUp will do the work of figuring out the data type and surrounding the data with the necessary delimiters.

Access also provides a useful function, BuildCriteria, that will accept a field name, a data type, and a field value and will create an expression of this sort: FieldName = "FieldValue" with the appropriate delimiters, depending on the data type. We've used this in our example 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 does most of the work of inserting the correct delimiters for you. To study the example, look at the BuildWhere function in frmQuoteTest's module.