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