Search The Web

Today's Headlines

Monday, February 15, 2010

Microsoft Access Tips & Tricks: Parameter Queries

Parameter queries are a powerful feature of Microsoft Access that enables query designers to write a query once, and use it for various purposes. The way this is accomplished is to make the query interactive, so that important aspects of the query's functioning can be provided to the query at run-time.

If you are interested, you can find my earlier posts on finding the median, the mode, the geometric and harmonic means, ranking every row in a query, selecting random rows out of a table, calculating running sums and averages, calculating running differences, creating histograms, calculating probability masses out of given data, calculating cumulative distributions out of given data, finding percentile scores, percentile values, calculating distinct counts and full outer joins.

Parameter queries have a lot of potential when used correctly. They can be used to reduce or eliminate the drudgery of writing several queries with minor changes in the criteria to pull up different types of information. They can also be used to insert dynamic information into tables, and to perform dynamic calculations as part of queries.

Let us look at some examples of parameter queries to understand their power. Consider a simple table in a database that contains bank transactions, for instance. Each transaction has an amount and a date. One could write a simple query to get all transactions for the past 30 days as below:

select * from Transactions where TransactionDate >= date() - 30

In the query above, we use the function date() to get today's date, and subtract 30 from it to get the date 30 days back. We then pull out all transactions with a transaction date greater than that date.

However, if different users of the database wanted to see history going back to different number of days, then the query has to be modified by each user individually, or you have to create separate queries for each person who needs a new date range. This is where parameter queries come in handy. Instead of hard-coding the 30 day range, parameter queries can make the date range dynamic. They can, at run-time, ask the user for the date range and then use it to pull up the appropriate transactions from the table.

To convert the query above to a parameter query, we would rewrite it as below:

select * from Transactions where TransactionDate >= date() - [Enter number of days:]

Assuming that "Enter number of days:" is not the name of a column in the table, when Access encounters the term in the query, it automatically knows that this is a parameter, and not a fixed value. Therefore, when the query is run, a box will pop up with the prompt "Enter number of days:", and allow the user to enter the appropriate number of days. The query will then use that number of days as the range over which transactions are to be pulled out of the table.

That is all there is to parameter queries at the most basic level. There are obviously some rules to follow. But the rules are mostly based on common sense, so there is nothing onerous about following them. Note that in the query above, the name of the parameter is "Enter number of days:". We enclose it in square brackets because it contains spaces. The name of the parameter is also the prompt that Access shows when the query is run. Even though we used a parameter name that sounds like a normal English sentence, there is no rule that says we have to. Practically any combination of letters, numbers and spaces can be used as a parameter name. Underscores can also be used in the name of a parameter.

  • Don't use Access reserved words as parameter names
  • Don't use characters like square brackets, parantheses, ".", and other characters that may have special significance for Access, in the parameter names. If you restrict yourself to upper case and lower case letters, underscores, spaces and numbers, you should be fine. If you use spaces in your parameter names, remember to enclose the entire parameter name in square brackets
  • Don't use the names of any columns in the table(s) used in the query as parameter names. Access will then not realize that you intend to use a parameter, and instead use the value in that column to run the query!
  • Note that the parameter name is the prompt shown to the user when the query is run. So, use sensible parameter names that provide the user with guidance as to what to enter. While a parameter name such as "SN235uitE" is perfectly acceptable to Access, it may not be quite as acceptable to the user who will, in all probability, have no clue what this parameter is for and how it used in the query!
  • If possible, and where there may be scope for confusion, make the data type and format of the parameter apparent in the parameter name. For instance, instead of a parameter name such as EnterDate, try a more explanative "Enter Date in mm/dd/yyyy format"

Now that we know the basics of a parameter query, where can we use parameters in a query?

The answer is that parameters can be used wherever we would normally use literal values during the running of a query. So, you can use a parameter instead of hard-coded values in the WHERE clause of a query. You can use a parameter instead of hard-coded values in the HAVING clause of a query. You can also use a parameter instead of hard-coded values in calculated fields of a query.

One notable exception to the ability of parameters to replace hard-coded values is in the TOP clause of a query. Access does not allow you to make that number a parameter. Thus the query below would cause a syntax error in Access:

select top [Enter Number of Records to Show:] * from Transactions
order by TransactionDate desc

We have already seen how a parameter can be used in the WHERE clause of a query. Let us look at an example of a query that uses a parameter in the HAVING clause.

select TransactionDate, sum(TransactionAmount) from Transactions
group by TransactionDate
having count(TransactionAmount) > [Enter number of transactions per date:]

The query above, when run, would prompt the user with the phrase "Enter number of transactions per date:", and use the number entered to show only dates where the number of transactions exceed the parameter value entered.

As mentioned before, you can also use a parameter to perform calculations in a query. For instance, if you wanted to show a certain percentage of each transaction amount as a calculated field in a query for any reason, you could write a query as below:

select *, TransactionAmount*[Enter Percentage:]/100.0 as TransactionPercent
from Transactions

In this case, the user would be prompted to enter the percentage to be used in the calculation above when the query is run. The percentage entered by the user would then be used to perform the calculation and the appropriate results would be displayed.

One is not restricted to using a parameter just once in a query either. Any parameter can be used any number of times in a query, and the user will have to enter the value for the parameter just once. The same value would be used in all places where the parameter is used in the query. For instance, the query below is perfectly legal:

select *, TransactionAmount - [Enter Discount Amount:] as DiscountedTransactionAmount
from Transactions where TransactionAmount - [Enter Discount Amount:] > 0

When you use the same parameter more than once in a query, make absolutely sure that you use the exact same spelling for the parameter name in all places. Otherwise, Access will think they are different parameters and prompt the user more than once with the different prompts.

You can also use multiple parameters in the same query without any problems. For instance, the query below is perfectly fine in Access:

select *, TransactionAmount - [Enter Discount Amount:] as DiscountedTransactionAmount
from Transactions
where TransactionAmount - [Enter Discount Amount:] > [Enter Minimum:]

So is this one:

select * from Transactions
where TransactionDate between [Enter First Date:] and [Enter Last Date:]

Parameters can also be used as part of any Access functions you choose to use. The query below illustrates this:

select * from Transactions
where left(TransactionType,4) = [Enter first four letters of transaction type:]

The query above will match the first four letters of the transaction type (using the left() function) against the 4 letters entered by the user and display matching transactions. You can also write the query as below so that the matching can be performed on any number of letters:

select * from Transactions
where left(TransactionType,[Number of Letters To Match:]) =
[Enter first few letters of transaction type:]

You can also use wildcards with parameters as below:

select * from Transactions
where TransactionType like "*" & [Enter letters to look for in transaction type:"] & "*"

The query above combines the letter-sequence entered by the user in response to the prompt with the wildcard "*" on either end and then compares it to the TransactionType using the keyword LIKE to find matches.

As you can see, parameters are quite flexible and powerful. Your ability to use them is only limited by your imagination!

Sometimes, you might require a parameter query to ignore the parameter and pull up all rows in a table when the user does not want to enter a value as a parameter. For instance, consider the parameter query below:

select * from Transactions
where TransactionAmount >= [Enter Minimum Amount To Display:]

There may be instances when the user just wants to see all transactions in the table regardless of the transaction amount. In that case, he can enter what he considers a very low value as the parameter value when prompted (such as -100000000000), but there is always the possibility that some transaction could get filtered out in spite of the user's caution. What you need is for the user to be able to signal that the parameter value should be ignored when running the query and the query should return all rows in the table.

The way to achieve this is to instruct the user to not enter a value for the parameter when the prompt is presented to him/her, resulting in a NULL value for the parameter. Then, the query can be modified as below to check whether the parameter value was NULL and in that case, display all rows in the table:

select * from Transactions
where (TransactionAmount >= [Enter Minimum Amount To Display:]
or [Enter Minimum Amount To Display:] is NULL)

When the user dismisses the parameter prompt without providing a value for the parameter, the parameter value becomes NULL. Thus, the second of the two conditions linked by OR in the WHERE clause above becomes TRUE for all rows in the table, thus enabling all of them to be displayed as part of the result set.

This technique will work for multiple parameter queries also. Consider the query below that pulls up transactions in a given date-range:

select * from Transactions
where (TransactionDate >= [Enter First Date:]
and TransactionDate <= [Enter Last Date:]

Notice that we did not use BETWEEN to demarcate the acceptable dates specifically so that we could individually invalidate each of these conditions based on whether the user chose not to enter a value for either of the parameters.

select * from Transactions where
((TransactionDate >= [Enter First Date:] or [Enter First Date:] is NULL)
and (TransactionDate <= [Enter Last Date:] or [Enter Last Date:] is NULL))

In the case of the query above, the user could choose to enter both dates when prompted, or dismiss either or both of the prompts without a value, making the corresponding parameter value NULL. When the "Enter First Date:" parameter becomes NULL, the query does not limit results by a minimum date because the condition to the right of the first OR in the WHERE clause becomes TRUE for all rows in the table. When the "Enter Last Date:" parameter becomes NULL, the query does not limit results by a maximum date because the condition to the right of the second OR in the WHERE clause becomes TRUE for all rows in the table. If both parameters take on NULL values, then the query pulls up all rows in the table regardless of the value of TransactionDate because both conditions to the right of the OR in the WHERE clause take on the value TRUE, making the entire WHERE clause return TRUE for all rows in the table.

In general, the order in which users are prompted for query parameters is the same order in which the parameters are encountered in the query from beginning to end. For instance, in the case of the last query, the user will always be prompted for "Enter First Date:" first, and only then for "Enter Last Date:". But for useability considerations, you may want to change the order in which the prompts for the parameters are presented to the user. The way to do this is by declaring the parameters beforehand in the query. The order in which you declare the parameters then becomes the order in which the parameter prompts appear to the user when the query is run.

In the case of the last query, if you wanted to be prompted for last date before being prompted for first date, you would change the query as below:

PARAMETERS [Enter Last Date:] Date, [Enter First Date:] Date;
select * from Transactions
where ((TransactionDate >= [Enter First Date:] or [Enter First Date:] is NULL)
and (TransactionDate <= [Enter Last Date:] or [Enter Last Date:] is NULL))

Notice the keyword PARAMETERS that is used to declare parameters to Access. We then specify the parameters in the order in which we would like the user to be prompted for them (in this case, for the last date before the first date) along with the datatypes for each (in this case, both of them are of type Date). Notice, also, that the parameters statement is terminated by a semi-colon.

You can enter the parameters as shown above in the SQL view of the query. Or, in Design View, click on Query->Parameters from the menu bar (in Access 2003), and enter the parameter names and the corresponding data types in the box that pops up, in the order in which you want to be prompted for them. For some bizarre reason, Access will not allow you to switch between design and SQL view of a query once you have explicitly declared parameters for the query! I find it much easier to work in the SQL view of any query because it is a lot more flexible. So, if you think you might ever want to work in the SQL view of a query, it is better to switch to that view before declaring the parameters for the query, and do the parameter declaration by typing them in as above rather than using the parameter declaration box provided in the graphical interface.

We will conclude this lesson by briefly exploring how you would use a parameter query in VBA. Suppose the query above was saved in your database as qryTransactionSelect. When you execute the query using VBA, for instance, as in the code snippet below, Access will not prompt you for the parameters used in the query. Instead, all you will get is a cryptic error message. To be precise, you will get runtime error 3061, "Too few parameters. Expected 2." The error message may vary depending on how many parameters the query takes.

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("qryTransactionSelect")

To get around this problem, you have to use a QueryDef object to open the query and explicitly set the parameter values before executing the query. The code snippet below illustrates this:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set db = CurrentDb
Set qdf = db.QueryDefs("qryTransactionSelect")
qdf.Parameters(0) = date()
qdf.parameters(1) = #01/01/2010#
Set rst = qdf.OpenRecordset

This would set the value of "Enter Last Date:" to today's date, and the value of "Enter First Date:" to 01/01/2010 before the query is executed. Notice that the parameters are numbered from 0 rather than 1. Also, the order of the parameters is the same as the order in which the parameters would be prompted for if the query were run directly. In this case, this is very important because the PARAMETERS statement was used in the query specifically to change the order in which the parameters are prompted for when the query is run. This order is now the order in which parameters have to be passed to the query before executing it in VBA.

A safer approach would be to explicitly set the values of the parameters by name in the VBA. The example below illustrates this:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryTransactionSelect")
qdf("Enter First Date:") = #01/01/2010#
qdf("Enter Last Date:") = Date()

The example above can also be written as below:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryTransactionSelect")
qdf![Enter First Date:] = #01/01/2010#
qdf![Enter Last Date:] = Date()

Note that even though, in the above code snippets, we have hard-coded Date() (for today's date) and 01/01/2010 for the last and first dates to be used, this is not necessary. These values can come just as easily from form fields, thus enabling the users to enter the values they need in a form as one of the steps in the process of perhaps creating a report using the code above.

Hope this post has been helpful in solving any problems you might have had with parameter queries in Access. If you have any problems or concerns with the queries or code in this lesson, please feel free to let me know by posting a comment. If you have other questions on Access that you would like me to address in future lessons, please feel free to let me know through your comments too. Good luck!

5 comments:

gez-ye-ic said...

Hi,
Sorry I write my question here because I couldn't find your email address or any place to write directly.
I read your post about losted flags and pageview and get back them. But I couldn't understand how I can success by my browser.
I also lost them and want to get back. Please , describe me what I have to do? I use, Internet Explorer 8.
If I obtain them, Appreciated too much.
Thanks advance.
Regards

Blogannath said...

Hi Gez-Ye-Ic, the person who retrieved his code from his browser was using a different browser that stores a lot of history which he had not cleared out. I don't think IE8 stores history for that long, so you may not be able to retrieve your code from the browser. You may have to generate new code.

In the future, after you generate the new code, make sure you save it somewhere safe so that if you lose it from your web page, you can go to the backup and retrieve it quickly. Always have multiple copies of things you don't want to lose! Good luck!!

dendalee said...

I have a question and I can't seem to find the answer. I was wonderinf if you might be able to help.

I have a very simple database.

Table1 stores some fields, among them the following:

CarsonHandPrevious
CarsonHandClosing
Day (which is the date of the entry)

I need to do this:
If the date today is Monday, then I need to view the last three records, i.e. Friday, Saturday, and Sunday's records.
If the date is Tuesday, Wednesday, Thursday, or Friday, I only need to see the record from the day before.

Any suggestions? Thanks!
deebaxter@ppg.com

Blogannath said...

This really has nothing to do with parameters. It seems to only depend on the day of week of the date on which the query is run. Thus I would use the weekday(date()) in a where clause. Probably something like below:

select * from mytable where datefield >= date() - iif(weekday(date()) = vbMonday, 3, 2)

That would select the last 3 days of data if day of week is monday, and choose the last 2 days of data if day of week is anything else.

Anonymous said...

Very informative and well written. Thank you.

Visitors Country Map

Free counters!

Content From TheFreeDictionary.com

In the News

Article of the Day

This Day in History

Today's Birthday

Quote of the Day

Word of the Day

Match Up
Match each word in the left column with its synonym on the right. When finished, click Answer to see the results. Good luck!

 

Hangman

Spelling Bee
difficulty level:
score: -
please wait...
 
spell the word:

Search The Web