VBA Programming
Using SQL “Where” Criteria Clauses in VBA
If you’re familiar with the SQL
“WHERE” clause, you will find it a little easier
to understand some of the details involved in the VBA version.
One of the major obstacles to
implementing criteria expressions in VBA
is that you don’t always want to just enter criteria directly into your code.
The book uses an example of looking for department # 20 (pg. 284),
but this means that the code only works if you want department # 20.
To look for another department, you would have to change the code.
Using an Access query, the
criteria can be changed easily enough,
but you still have to go into “Design” view to do it.
You could also use a “Parameter” query,
but VBA is more elegant.
VBA involves more work to do this, but the results are much friendlier
(at least for the user, if not for you)
VBA lets you customize the program
so that it adjusts to the user’s choices.
In VBA, you can store the criteria you want to use in a text variable
that can be customized according to choices the user makes,
such as selecting a department number for an employee report.
StrWhere = “DeptNo = ‘20’”
The
user’s choices are typically made by checking check boxes, selecting option
buttons,
and by entering certain data into text boxes. All of these can be analyzed by
your program,
and the program will use these choices to determine the results.
Before going
into the analysis of the user’s choices, we need to look at the way
to “assemble” the criteria string using the text string functions from chapter
3.
We also need
to look at the way VBA and SQL handle quoted strings differently.
VBA puts string literals in double quotes (“) and SQL uses single quotes (‘)
for sting literals.
This gets a little confusing when VBA is quoting an SQL string literal because
you need to use both single- and double-quotes to get the job done.
Take this
SQL “WHERE” condition which looks for the value numeric constant 20
in the DeptNo field, as used in the Access SQL view :
(Note that
the text string value 20 is enclosed in double-quotes here)
WHERE
DeptNo = “20”
But the VBA
SQL expression is coded this way,
(and does not use the keyword “WHERE”) :
(Note that
the text string value 20 is enclosed in single-quotes here)
DeptNo = ‘20’
To assign
this string to a VBA text variable, enclose the entire string in double-quotes :
Dim StrWhere As String
StrWhere = “DeptNo = ‘20’”
Note that
the entire VBA SQL expression (including the single-quotes)
is completely ‘nested’ inside the double-quotes.
(DeptNo is a text field, even though it looks like it’s
numeric,
and therefore the value is enclosed in quotes.)
The variable
StrWhere contains the criteria
to be
used for the DoCmd.OpenReport
“Criteria” argument.
This
criteria uses a constant (‘20’), but you may want to use
a value that can be changed when the user makes a selection on the form,
such as fro a list or combo box. To do this the value ‘20’ must be replaced
with
the name of the object that contains the user’s selection.
If a list of
available department numbers is stored in a combo box named cboDept,
then the VBA SQL assignment statement can be re-written like this :
StrWhere = “DeptNo = ‘” & cboDept
& “’”
This line
above shows how the program code is written.
The text
string that is stored in strWhere is
: DeptNo = ‘cboDept’
Can you see that “DeptNo = “ & “’” is exactly the same as “DeptNo = ’” ?
The
statement instructs the program to concatenate
the field name DeptNo = ‘ and a
single-quote ’
and to insert
the
value selected from the combo box (cboDept) in between them.
For
example :
If department # 20 is selected in the
combo box, this results in the following value
being stored in the variable StrWhere, as shown here :
(This is
what the computer ‘sees’)
DeptNo = ‘20’
If department # 50 is selected in the combo box, this
results in the following value
being stored in the variable StrWhere, as
shown here :
DeptNo = ‘50’
What does this statement actually do, anyway ?
The
purpose of this instruction is to find the row in the recordset
that matches the value of the combo box selection
and include it on the report.
Suppose
we have a criteria string that contains a criteria expression like this :
StrWhere = “ And DeptNo = ‘”
& cboDept
& “’ And Terminated
= ” & chkTerm
But it contains an extra “ And ”
at the beginning of the expression,
which will cause an error if we don’t “cut it off.”
This “extra” part is 5 characters long
(don’t forget the spaces on either side of it)
How
can we do that ?
Remember the Left, Right and Mid functions
? These are used to “parse” text
strings in order
to separate the parts of data we want to
use, from the parts of data we don’t want to use.
In this case, the Mid function is the tool of choice,
since its purpose is to select part of a text string,
starting at a specified position in that string.
And that’s precisely what we want to do here :
We need to use the part of the criteria string without the first five
characters.
In other words, we only want the part of the
criteria string
that starts at the sixth character position.
Since contains the criteria, the
following code should do the trick :
StrWhere =
Mid(StrWhere, 6)
Now the StrWhere the contains the
following expression :
StrWhere = “DeptNo = ‘” & cboDept
& “’ And Terminated
= ” & chkTerm
What
does this new version statement actually do, anyway ?
The purpose
of this instruction is to find each row in the recordset
that matches the value of the cboDept
combo box
selection
and
also
matches the value of the chkTerm checkbox,
as specified by the user’s choices,
and include all matching rows on the report.