لطفا منتظر باشید ...
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 stringsfor
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:
- In the First Name text box, enter A. When you press Return, the form
builds the appropriate SQL string and filters the list box. Note in
the SQL string that the value you entered is surrounded by quotes.
(This is the state in which Figure 7-1 was
captured.) - In the Birth Date text box, enter 3/13/60. Again, the form should
filter the data (down to a single row). Note that the SQL expression
must have "#" signs around the date
value you entered. - Press the Reset button to delete all the data from the four text
boxes. That will again fill the list box with all the rows. Enter the
value 8 in the ID text box, and then press Return. Note that the SQL
string this time has no delimiter around the value that you entered.
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 piecesthe 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.
|
