INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How To

Use "filter" to send criteria to a report by randysmid
Posted: 28 Oct 02 (Edited 23 Oct 03)

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    



Back to Microsoft: Access Reports FAQ Index
Back to Microsoft: Access Reports Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close