How To Build And Manipulate Parameter Queries In Ms Access 2013 & 2016

Parameter query is the most versatile query type out of all the available queries. Creating a parameter query is like creating a regular select query. The only difference is that where you would normally enter your query criteria, you instead enter square brackets [ ]. This tells access that the user will be prompted for the criteria in this field. If you want to customize the message that is used to prompt the user you can type your own custom message between the square brackets.

This type of query can only be created through Query Design view just like the Action Queries.

This is the part 3 of chapter 5 of the MS Access Tutorial Series. I will fully explain how to manipulate parameter queries and wildcards.

See the Part 1 (Introduction to Advanced Query in MS Access) or see the Full Course Content of this Access tutorial. See Part 2. 

PARAMETER QUERY

This type of advanced query serves as an interactive filter that allows you to specify a different filter criterion every time you run the query. You can design the query to prompt you for more than one piece of information; for example, you can design the query to prompt you for two dates. Access will then retrieve all records that fall between those two dates.

Parameter queries are helpful when used as the basis for forms, reports and data access pages. For example, you can create a monthly earnings report based on a parameter query. When you print out the report, Access displays a dialogue box asking for the month that you want the report to cover. Once you enter the desired month, Access will print the appropriate report.

Also, if you are always filtering your table or database based on a particular field, then building a parameter once for all makes the job easier for you.

For example, a company Manager who always filters his worker’s payroll table based on their qualification will have to create a parameter query on the QUALIFICATION field once. 

Then each time he wants to view the records of workers with a particular qualification, he will just open the query in datasheet view and enter the criteria in the dialogue box that appear and MS Access filters the database table and returns the matching records.

To Build or Create a Simple Parameter Query:

1. Click the Create tab, then click the Query Design icon located in the Queries group.

2. The Show table dialogue box appears. Double click on the table(s)/query you wish to use as data source(s) for your parameter query. Then close the Show Table dialogue box as shown below.

3. Select the fields that you would like to include in the query. You may also select all fields depending on your choice.

4. In the Criteria row for any of the fields you wish to base your query filter, type a pair of square bracket ([]). Referring to the above payroll table, I will use the QUAL field as shown below.

5. Click the View icon or Run icon located in the View group under the Home tab to run the query. The parameter query dialogue box will pop up asking you to enter a criteria for that field. Simply enter any criteria of your choice and click OK as shown below.

6. Access will run the query and retrieve your data from the database. Save the query with a unique name if you wish.

NOTE:

If you try running a parameter query and get a message about Replication IDs, it is probably because you uses curly brackets { } instead of square brackets [ ]. To correct this, return to query design view and use the square bracket.

HOW TO ADD A CUSTOM MESSAGE TO YOUR PARAMETER QUERY

I will now show you how to put a custom message in the criteria prompt. Custom messages are very useful in parameter especially if you are designing the query for other people. Also, it makes the query look professional.

To Add a Custom Message to your Criteria Prompt:

1. Return to Query Design view.

2. Enter a custom message between the square brackets in the Criteria row for any of the fields you wish to base your query filter. In the above example, my expression under the QUAL field or column will look like this: [Enter a Qualification] as shown below.

3. Now, run the query. This time, the Enter a Parameter dialogue box will display the custom message you entered as shown below.

4. Enter any criteria for that field and click OK. Save the query and close it.

HOW TO CREATE A PARAMETER QUERY WITH MULTIPLE CRITERIA

As you saw in advanced filter query that you can have criteria on as many fields as you like. This applies for Parameter queries as well. If you have more than one parameter criteria in a query then a prompt will appear for each criterion, one after the other before the query results appear. You can also use parameters for range criteria.

For example, the University Management may like to see the records of students that fall within a certain age range, say 20 to 25 from the Students Record table. Then he will need to design a parameter query that will accept two criteria ranges (minimum and maximum).

To Create a Parameter Query that Accepts Multiple Criteria:

1. Click the Create tab, then click the Query Design icon located in the Queries group.

2. The Show table dialogue box appears. Double click on the table(s)/query you wish to use as data source(s) for your parameter query. Referring to the above example, I will select the Students Record table. Then close the Show Table dialogue box.

3. Select the fields that you would like to include in the query. You may also select all fields depending on your choice.

4. In the Criteria row for any of the fields you wish to base your query filter, type a criteria range in this format: >=[Enter Custom Message for Minimum Value] And <=[ Enter Custom Message for Maximum Value]. Referring to the above students’ record table, I will type the following expression under the AGE field in the Criteria row: 

>=[Enter Minimum Age] And <=[Enter Maximum Age].

NOTE:

When you are working with long criteria like this, it can be difficult to edit because of the limited space.

To make it easier, right-click on the Criteria row and click Zoom. You will then have a nice large window to edit your criteria as shown below. When you are done you can click OK to confirm the changes.

5. Now run the query. Two Enter Parameter Value dialogue boxes will pop up one after the other, the first will ask you to enter the minimum criteria range while the second will ask for the maximum criteria range. Enter the range for each prompt and click OK as shown below.

6. Then MS Access runs the query and returns the records that fall within the specified criteria ranges from the database. Save the query with a unique name if you wish, then close it.

HOW TO USE WILDCARDS IN PARAMETER QUERIES

You can also use a combination of wildcard characters in combination of other functions or expression as your criteria when designing parameter queries in MS Access. The most commonly used wildcard character is the Asterisk character (*). This character simply means ALL. The meaning may slightly vary depending on its position in the expression.

To Use the Asterisk (*) Wildcard in a Parameter Query Criteria Expression:

1. Click the Create tab, then click the Query Design icon located in the Queries group.

2. The Show table dialogue box appears. Double click on the table(s)/query you wish to use as data source(s) for your parameter query. Referring to the above example, I will select the Students Record table. Then close the Show Table dialogue box.

3. Select the fields that you would like to include in the query. You may also select all fields depending on your choice.

4. In the Criteria row for any of the fields you wish to base your query filter, type a criteria range in this format:

Like [Enter the Initial Letter of the First Name] & *

The ampersand (&) character will join the user’s typed criteria on to the asterisk (*) wildcard. This will mean that the user will only need to enter the first few letters of a first name to get the results.

I entered the above expression in the FIRST NAME field of the Students’ Record table and then ran the query. The criteria prompt displays. Enter only the initial letter of the first names of the records you want to view as shown below.

Then Access displays the records of students whose first names matched the letter you entered in the criteria prompt

OTHER WILDCARD CRITERIA EXPRESSIONS

These are some variations of the above wildcard expression, the fields where they can be applied and their descriptions.

1. 

Field: FIRST NAME
Criteria: Like * & [LAST NAME] & *
Description: Adds a wildcard before and after what the user types.

2. 

Field: COUNTRY
Criteria: Like * & [COUNTRY] & *
Description: Adds a wildcard before and after what the user types.

This is the end of chapter 5 of the MS Access tutorial series. 

Recommended Textbook

Learn Dynamic Web Development And DBMS:

With HTML, CSS, PHP In Dreamweaver CS5 And CS6, MySQL And MS Access 2013 And 2016 By David Max

Click To Buy This Book Now

Click Here to know more about the book.

In chapter 6, I will explicitly explain SQL and how it used in RDBMS (Relational Database Management Systems) like Access & MySQL.

Was this tutorial helpful to you?

Inform your friends about this post by clicking the share button below. Comment below if you are hooked up along the installation process.

Also click Here to subscribe for free so that you will get our latest game updates in your email.