Criteria/Parameter Prompt for Multiple Values

[Allan]

Can someone let me know what the format for a criteria
prompt would be to allow the user to input more that one
value for a given field? For example - I want to run a
departmental report and want the user to be able to input
more than one department (comma separated) at a time. To
enter a single value, I realize the format is [Enter
Department Number:] in the criteria row, but how to do it
for multiple values (and all if possible)?

Allan

 

[Dale Fye]

Allen,

In your criteria row of the query grid, for the Department# column put:

Criteria: IN (3, 4, 5, 6)

This will set your query to include records from each of those departments.
The down side, is that you cannot enter that as a value to a parameter
query. You can enter it directly in the criteria row of the query grid, or
you can build your query dynamically, and then populate the querydef before
running it. But you cannot write a query that looks like:

SELECT *
FROM yourTable
WHERE DeptID = [Enter department values]

and expect it to run properly if you type "IN (3, 4, 5, 6)" when the prompt
displays.

A technique I prefer to use is to include a multi select listbox on my form
that displays all of the department names. This listbox is populated from a
Departments table that contains a Yes/No field (IncludeThis). In the click
event of the listbox, I put some code (below) that updates the IncludeThis
column of the Departments table.

Private Sub list_Departments_Click()

Dim strSQL As String

strSQL = "UPDATE tbl_Departments " _
& "SET IncludeThis = NOT IncludeThis " _
& "WHERE DeptNo = " & Me.list_Departments.Column(0)
CurrentDb.Execute strSQL

End Sub

Then, I modify the query used for the report to include a link to this table
on the DeptNo, and add a criteria where IncludeThis = True. Then, when I
close the form, I update the IncludeThis column in the table to false for
each record in the table.

HTH
Dale