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