Create a Parameter Query to accept multiple values

Parameter queries are a quick way to create dynamic queries where the result changes depending on user input. If you are not familiar with parameter queries, you can review my article on Parameter Query Basics.

The following SQL statement is an example of  a parameter query.

SELECT Title, FirstName, LastName, Position
FROM Employees
WHERE Department=[Enter Department]

The above query will prompt the user to enter a department and the result will show all the employees working for that department. So, users are free to enter any department they are interested to see, each time they run the query. But what if the user wants to see all the employees for more than one department?

Problem

Typically, in a non-parameterized query, the SQL statement might look something like this.

SELECT Department, Title, FirstName, LastName, Position
FROM Employees
INNER JOIN Detpartments
ON Employees.DeptID=Departments.DeptID
WHERE Employees.DeptID In(1,3,5)

In the above example,  we switched to using the foreign key (DeptID), which would be a more likely scenario anyway and also to make demonstrating the technique being presented a bit simpler to explain. Also, please note the In() clause in the above example is a shorthand for the following criteria (meaning, it's functionally the same).

WHERE Employees.DeptID=1 OR Employees.DeptID=3 OR Employees.DeptID=5

So, our goal is to prompt the user to enter multiple DeptID using a parameter. One might be tempted to try the following as a query criteria.

WHERE DeptID In([Enter DeptID])

and expecting the user to simply enter 1,3,5 when prompted. Unfortunately, this seemingly straightforward approach will not work because Access will interpret the entire input as a String or Text. So, entering 1,3,5 does not mean 1 or 3 or 5; but rather, a string value of "1,3,5" - and we know there won't be any DeptID of "1,3,5" because DeptID is a numeric field. So, the query will result in an empty set because the intended criteria fails. Of course, the obvious solution is to use the following criteria.

WHERE DeptID=[Enter Dept1] OR DeptID=[Enter Dept2] OR DeptID=[Enter Dept3]

The above approach will work, but the user will be prompted to enter a single DeptID three times, which would be fine but what if the user wants to know more (or less) than three DeptID? As you can see, this approach is not very flexible.

Solution

Instead of using the above approach, we could try either of the following approaches.

WHERE InStr(";" & [Enter DeptID (separated by a semicolon ';')] & ";", ";" & DeptID & ";")>0

or

WHERE ";" & [Enter DeptID (separated by a semicolon ';')] & ";" Like "*;" & DeptID & ";*"

When the user runs the query and gets the prompt, we are expecting the user to enter something like 1;3;5, which in our criteria will be converted into ;1;3;5; (I prefer to use semicolons but using commas would work the same way).

The first approach uses the InStr() function to search the current record's DeptID, encapsulated within a pair of semicolons, within the user's input string from the parameter prompt. If a match is found, the result of the function should be a number greater than zero (0), which would make the criteria evaluate as True and include the current record in the query's result set.

The second approach simply uses the Like operator with a pair of wildcard characters to accomplish the same effect as the first approach. I prefer to use the second approach because it is simpler and avoids a function call, but either approach will work as intended.

Caveat

Unfortunately, this solution is not perfect. The first drawback is you'll have to "train" your users to enter their input using the exact format your query is expecting (e.g. 1,3,5 or 1;3;5 or 1 OR 3 OR 5). Otherwise, the query will not work if the user enters their input using any other format (e.g. user enters 1,3,5 but you expect 1;3;5). Adding the expected format in the parameter prompt, as in the example above, should help a little bit in reminding the user what format is expected.

The other drawback I would like to point out affects any parameter query, in general. As I mentioned in my article on Parameter Query Basics, there is really no way to validate the user's input when using a parameter query. Your query might be asking users to enter a DeptID in the parameter prompt but nothing will stop them from making a mistake and end up entering a DeptName instead. For this reason, it is considered "best practice" to use an unbound form to accept user input for your query parameters. Using a form will allow you more control over the user's input. You can easily validate their input when you use a form.

The last point I'll mention, if you do end up using parameters in your query, is to make sure you declare the data type for each parameter. This is more applicable when each parameter is a single value. To declare the parameter's data type, you can click on the Parameters button on the Design Ribbon to open the Query Parameters window.

To declare the parameter data types manually, you can add the PARAMETER clause at the beginning of your query's SQL statement. For example:

PARAMETERS [Enter DeptID] Long, [Enter Department] Text ( 255 );
SELECT ...
FROM ...
etc...

Please note, it is very important to end the PARAMETER clause with a semicolon, or you will get a syntax error. Also, the PARAMETER clause is typically required in Crosstab queries.

Conclusion

Parameter queries allow us to create semi-dynamic queries, which could help reduce the number of queries we need to create if all we need to change is the criteria condition of the query, based on user input. It is even possible to create a parameter query that accepts multiple values from the user. However, parameter queries have some limitations that it is highly recommended to use input forms whenever possible. Still, parameter queries are good as quick solutions for those times when the requirement is simple enough with very low chance of user input errors.