Share this Page URL

Chapter 20. Building Reports in an Acces... > Working with Input Parameters - Pg. 664

Building Reports in an Access Project 664 You can change the Server Filter property to the criteria shown above and then change to the Report window. Notice that the report now displays three companies instead of two. You can also specify criteria as complex as necessary to get the job done. This report uses a sub-report to display the contact information for each company. If you want to filter on the contact information, you must use a subquery. (See Article 1, "Understanding SQL," on the companion CD for details about using subqueries to filter data.) For example, if you want to list all companies that have a contact who is contact type 'Customer' in the state of Pennsylvania, you could specify the following filter: CompanyID IN (SELECT CompanyID FROM tblCompanyContacts INNER JOIN tblContacts ON tblCompanyContacts.ContactID = tblContacts.ContactID WHERE tblContacts.ContactType = 'Customer' AND tblContacts.WorkStateOrProvince = 'PA') Using the above filter returns the two companies in Pennsylvania that have one or more contacts categorized as customers. Tip Access provides the option to specify both server filters and regular filters in your reports. Remember that server filters are always more efficient because the server applies the filter before returning the data. Unless you have a very compelling reason to filter the data after it is returned to the report, always use server filters instead. Working with Input Parameters Server filters are great if you need to filter the data from a table or view. However, they don't work if your recordset is an in-line function or a stored procedure. Fortunately, in-line functions and stored procedures can use input parameters to control the data they return.