Access Cookbook - Ken Getz [139]
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