I love writing VBA code but I am convinced that, many times what I am trying
to accomplish can be done in a more efficient, graceful and elegant way using
SQL queries. Over the last few months, I have tried to develop my applications
with this in mind. The following article is a summary of problems I have
been able to solve by using fascinating query tricks. Here is an
overview of what we will cover:
- How to pass a variable parameter to a query calculation
-
How to add "<All Employees>" to a list of
employee names - Use UNION query to add a Totals row to a dataset output
These common development tasks
pop up all the time and some of these solutions I have been using for
years. They are the kind of things that are good to have
in your toolbox. If you don’t already have a solution for these issues, download the demo application for this article
and store it with your stock code.
How to pass a variable parameter to a query calculation
VBA Solution:
The first tab of my demo form shows three different ways you can perform a
price increase calculation. In this example, I want to add a percentage
to the price of a book. The default is 10%, but this parameter should be
adjustable. The following shows two ways to accomplish this: one uses a
VBA function and the other is strictly a query solution.
The following SQL Statement shows how the price could be calculated using
a hard-coded value. Of course, this is not very flexible or easy to
adjust, so we want to make it dynamic.
SELECT title_id, price, price * 1.10 AS Adj_Price FROM titles;
One way to make this calculation dynamic is to substitute a variable for the
price increase percentage, except Access queries are not able to read
variables, not even publicly declared ones. The only way to pass
a variable to a query is by means of a function that returns the
variable, like this:
SELECT title_id, price, price * FetchPricePct() AS Adj_Price FROM titles;
In this particular demo, the method FetchPricePct() returns the value of a
public variable. In order for this to fit into your code paradigm, you
will need to have a function that also sets this public
variable to some value. Alternatively, you could put a reference directly
to a control on a form, like this …
SELECT title_id, price, price * Forms!frmMain!txtPct AS Adj_Price FROM titles;
However, this creates a problem if the form is not open, or if the text box is
empty, or if it doesn’t contain a number. You could, of course,
write inline code to handle those exceptions in the query, but it gets
kind of crowded …
SELECT title_id, price, price *
IIF(IsNumeric(Nz(Forms!frmMain!txtPct,1.1)),
Nz(Forms!frmMain!txtPct,1.1), 1.1)AS Adj_Price FROM titles;
I have done this, and it works, but it’s not pretty. The IIF() function
first checks to see if the value is numeric, substituting our default
(1.10) for NULL, since NULL is neither numeric nor non-numeric. If this
test passes, then it uses the text box value but if it fails (the text
"one point one zero" is not equal to 1.10) our default is
supplied. This kind of logic is better placed in a function which is easy
to modify and even easier to reuse. That is why I would discourage you
from doing what is shown in the code snippet above.
Also, I’ve been known to do tricky things in the function, such as read values
stored in the registry or search through open forms until I found one that has
the particular ID I require. That way the query is not tied to a specific
form. If frmEmployee isn’t open, maybe the EmplNo I seek can be
found on a different form, such as frmTimeSheet. Your functions can
become very reusable.
There is one problem with this paradigm: Access queries called from ASP
web pages cannot resolve the source of the function. They appear
undefined to the ADODB layer and the query fails. That brought me to my
query based solution for this problem.
Query Solution:
What I really needed, I thought, was a table with a field that contained the
price increase value. If I could join this table to my Titles table, I
could replace the function with a simple field value. However, on
what column could I join them? No column, the answer turns out to
be. You create a Cartesian Product of the two tables by referencing both
but assigning no kind of join. The SQL for this solution looks like
this:
SELECT title_id, price, price * Pct_Increase AS Adj_Price FROM titles, PriceVariable;
There is a caveat: the tblPriceVariable table may have only one row. If,
for example, it contained two rows, then two records would be generated for
every row in the Titles table. Notice the right-most query result pane in
the Figure 1 below and you will see what I mean. Another thing you may
notice from the image is that the tables using the Cartesian Product do not
expose an "Add Record" line and the Add Record button is grayed
out. Because of the nature of the join, this recordset is not
updateable. For my ASP application, that was no problem, but if you need
an updateable recordset, the Cartesian Product idea will not work for you.
