Use "filter" to send criteria to a report

Many Access developers use forms to allow the user an opportunity to select criteria for printing reports. For instance, a user may only want to print customers from California.

Let's say that our form has a listbox called "lstState", and is populated with state abbreviations. Our customer table also uses these same state abbreviations.

Our report recordsource can either come directly from a table or a query, but we will need to know the exact name of the state field. Let's say it is "fldState".

Usually we use a command button to run the report. Here is a sampling of the command buttons' click event to set the filter, then run the report.

Single Selection example
strFilter = "[fldState] = '" & Me!lstState & "'"
DoCmd.OpenReport "rptCustomer", acViewPreview, , strFilter

Here is an example of a filter that adds another criteria (only print customers from California whose last name is Smith):

strFilter = "[fldState] = '" & Me!lstState & "' and " & _
"[fldLastName] = '" & Me!LastName & "'"

As you can see, single quote marks must be wrapped around any field values that are strings.

Multiple selection example
In code, you can send all the selected rows to the report as a filter. Here is the code that will do that:

'place this code in the clicked event of your command
'button which starts the report, e.g., "Run Report"
' - presume the listbox is called lstCustomers
' the recordsource for the report is a query
' containing all the field elements you need
Dim strFilter as string
Dim varItem as variant
' loop through listbox items selected
For Each varItem In Me!lstCustomers.ItemsSelected
strFilter = strFilter & "[CustomerNumb] = '" & _
Me![lstCustomers].ItemData(varItem) & "' OR "
Next ' continue loop
'
' the next bit of code will subtract out the last "OR"
If strFilter <> "" Then
strFilter = Left(strFilter, Len(strFilter) - 4)
else
MsgBox "You did not select any customer(s)."
lstCustomers.SetFocus
Exit Sub
End If
'
' now, run the report using strFilter to pass a string
' containing the needed customers
DoCmd.OpenReport "CustomerReport", acPreview,,strFilter



Feedback, comments?
Randy Smith, MCP
rsmith@cta.org