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.
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.
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.
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.
In chapter 6, I will explicitly explain SQL and how it used in RDBMS (Relational Database Management Systems) like Access & MySQL.
Recommended Textbook
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.
With HTML, CSS, PHP In Dreamweaver CS5 And CS6, MySQL And MS Access 2013 And 2016 By David Max
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.
No comments:
Post a Comment
WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!