Filtering Query Results with Parameter Queries

Sometimes you run a query, and it returns more data than you can possible go through. Sometimes you actually need all of that data to get the best analytical results. More often though, you are really only looking for a small fraction of the information provided. Fortunately, Microsoft Access gives you a number of different ways to filter results so that you can narrow down the data into manageable sizes.
A parameter query is a query that has parameters to filter the information that is returned in the result. You have complete control over these kinds of queries and can update and change them to get whatever information you need at this or any other time. Access remembers your parameter queries, so when you open a parameter query, you will get a prompt for the search term.
It is an incredibly simple, but powerful way of pulling data on the fly. There are a number of things you have to do to create these queries and have them be reliable. Access also lets you add parameter queries to apps, although the process is slightly different.
How to Add a Parameter Query
Adding a parameter query is relatively simple as long as you know what it is you want to use as your filter. The best starting point is to write down the queries that you use often, such as an end date or customer ID, that way you can create all of the parameter queries you are likely to use.
First let’s go over how to add a filter.

1. Open the query to the one you want to add the parameter query to. Make sure it is in Design View.

1. Click on the Run icon.
2. Enter the information you want to check, such as 1/1/15.
3. Click the OK button.

Your results are now filtered to include information for 1/1/15.
Adding a parameter query is similar.

1. Make sure you are in the correct query in Design View.
2. Click on the Create tab, then the Query Design (on the Queries menu).

1. Select the type of query you would like to add.

• Select
• Make Table
• Append
• Update
• Crosstab

The rest of the steps should work for any query, but let’s click Select for now.

1. Click on the criteria row (in the bottom frame).
2. Type [ ] and enter what you want the query to filter, such as [Enter the Employee ID]. The information you entered is your parameter, or the identifier.
3. Highlight the text between the brackets and copy it (do not copy the brackets).
4. Click on Parameters from the ribbon (under Show/Hide).
5. Paste the text in the first column.
6. Click on the drop down arrow in the Data Type column and select the type of data. This should match the type used in the table field.
7. Click OK.
8. Run the query.

Now when you want to use a filter, you can use this parameter as the filter. It ensures that users will enter the right type of data, and when a user enters the wrong data type an error message will pop up. Users will have to enter a full, valid value for it to work. For example, if there are no employees with the entered ID or if they don’t enter all of the numbers for the ID (such as entering 26 instead of 00000026), it will not pull the results.
All of the parameter types listed in step three work the same way.
Union Queries
You can also add query parameters to Union queries.

1. Open the query in SQL View (right click on the query tab and scroll down).
2. Look at the second bracket after the first SELECT statement. That will be the first thing to add in the WHERE statement.
3. Enter a line after the first FROM statement.
4. Type the following

WHERE [second bracket name] = [text you want to appear]

1. Repeat steps 2 through 4 in the second SELECT statement.

Here’s an example.
SELECT [Item ID], [Date of Order], [Customer], [Item], [Quantity]
FROM [Product Order]
WHERE [Date of Order] = [Enter date]
SELECT [Item ID], [Date Created], [Customer], [Item], [Quantity]
FROM [Product Purchases]
WHERE [Date Created] = [Enter date]
ORDER BY [Order Date] DESC
Once you do that, you can run the query and it will act just like the other parameter queries.
Using Wildcards and Logical Operators
If you want to give users more flexibility in their entries, you can make use of wildcards and logical operators.

Wildcards

Wildcards are incredibly handy tools for users, so you want to make them available as much as possible. It makes it easier to enter long strings of repetitive date (like Employee ID 00000026 can be entered as *26) or to cover all necessary data when a user doesn’t know the exact value.

1. Go to the query and open it in Design View.
2. On the Criteria row enter Like “*” and & for the areas where you want users to be able to use the wildcard. . For example, Like “*” & [Enter Employee ID].
3. Click on Parameters on the ribbon.
4. Enter the bracketed text exactly as you entered them into the Criteria row, omitting all of the wildcard information. So under the Parameter column you add Enter Employee ID. Like, *, and & are not necessary for this because it is showing the text that displays when the user runs the query.
5. Enter the data type.
6. Click on OK.

Always make sure to add quotation marks around the *. The & appends the user entered information to all existing entries. For example, 26 is appended at the end of all entries that end in 0026. So the resulting query will include 00000126, 00000226, 00001126, and so on. Anything Employee ID ending with 26 will be included in the final query. You can also add the “*” & combination at the end of the query as well – Like “*” & {Enter Employee ID} & “*” so that all instances of 26 are returned, no matter where it appears in the Employee ID.

Logical Operators

Logical operators let users enter a range for their searches, which is particularly useful for things like dates and numeric range values. Two of the most common are Between and And.

1. Go to the query and open it in Design View.
2. On the Criteria row enter Between [text] And [text]. For example, Between [Enter start date] And [Enter end date].
3. Click on Parameters on the ribbon.
4. Enter the bracketed text exactly as you entered them into the Criteria row.
5. Enter the data types for both identifiers.
6. Click on OK.

Now you can run the query and see how the parameters work for the user. You now get to enter two sets of dates so that you get more data with the filter.