Fascinating Query Tricks
December 17, 2004
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:
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
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.
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.