ACC2000: How to Reference Parameters in Reports (208630)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q208630
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SUMMARY

This article describes how you can reference the parameters of a report's RecordSource query or view. You can use this method to display the parameters in a text box in the report.

NOTE: This article explains a technique demonstrated in the sample file, RptSmp00.mdb. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

231851 ACC2000: Microsoft Access 2000 Sample Reports Available in Download Center

MORE INFORMATION

Steps for an Access Database

The following example demonstrates how to display the parameters from a report's RecordSource query in an Access database:

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

  1. Open the sample database Northwind.mdb and create a new query based on the Orders, Order Details, and Products tables. Include the OrderID and the OrderDate fields from the Orders table and the ProductName field from the Products table.
  2. On the Query menu, click Parameters. In the Query Parameters box, under Parameters, type Enter Start Date, and under Data Type enter Date/time. Press TAB to move to the next Parameter field. Under Parameters, type Enter End Date and under Data Type, enter Date/time. Click OK.
  3. Add the following criteria for the OrderDate field:

    Between [Enter Start Date] And [Enter End Date]

  4. Save the query as ProductReport, and then close it.
  5. In the Database window, click Reports under Objects, and then click New.
  6. In the New Report box, click AutoReport: Tabular, click the ProductReport query in the Choose the table or query where the object's data comes from box, and then click OK.
  7. When the AutoReport Wizard prompts for a Start and End date, click OK each time, and when the wizard presents the new report in Print Preview, click Report Design on the View menu.
  8. Add a text box to the report's header section.
  9. Set the ControlSource property of the text box to the following expression:
    ="Report Period From" & " " & [Enter Start Date] & " " & "Through" & " " & [Enter End Date]
  10. On the View menu, click Print Preview. When prompted for the Start Date, enter 08/08/97. When prompted for the End Date, enter 01/1/98. Note that when viewed in Print Preview, the report header now has the following text:

    Report Period From 8/1/97 Through 1/1/98

Steps for an Access Project

The following example demonstrates how to display the parameters from a report's RecordSource query in an Access project:

CAUTION: If you follow the steps in this example, you modify the sample Access project NorthwindCS.adp. You may want to back up the NorthwindCS.adp file and follow these steps on a copy of the project.

  1. Open the sample Access project NorthwindCS.
  2. Click Stored Procedures under Objects, and then click New.
  3. Type the following in the new procedure:
    Create Procedure ProductRepSp
(@BeginDate datetime, @EndDate datetime)
As

SELECT Orders.OrderID, Orders.OrderDate, 
    Products.ProductName
FROM Orders INNER JOIN
    [Order Details] ON 
    Orders.OrderID = [Order Details].OrderID INNER JOIN
    Products ON 
    [Order Details].ProductID = Products.ProductID
WHERE (Orders.OrderDate BETWEEN @BeginDate AND 
    @EndDate)
  4. Save the stored procedure as ProductReportSP, and then close it.
  5. In the Database window, click Reports under Objects, and then click New.
  6. Click Design View and click OK. Do not choose a source from the Choose the table or view where the object's data comes from box.
  7. Click Properties on the View menu.
  8. On the Data tab, in the Record Source field, type ProductReportSP.
  9. Close the Properties dialog box.
  10. From the field list box, drag OrderID, OrderDate, and ProductName to the details section of the report.

    NOTE: To take out unnecessary blank space from the detail section, you may need to move the page footer bar up closer to the fields that you added.
  11. Add a text box to the header section of the report.
  12. Set the ControlSource property of the text box to the following expression:
    ="Report Period From" & " " & [@BeginDate] & " " & "Through" & " " & [@EndDate]
  13. On the View menu, click Print Preview. When prompted for the Start Date, enter 08/08/97. When prompted for the End Date, enter 01/1/98. Note that when viewed in Print Preview, the report header now has the following text:

    Report Period From 8/1/97 Through 1/1/98

REFERENCES

For more information about referencing query parameters, click Microsoft Access Help on the Help menu, type query parameters in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Modification Type:MinorLast Reviewed:7/14/2004
Keywords:kbhowto kbusage KB208630
©2004 Microsoft Corporation. All rights reserved.