ACC: How to Reference Parameters in Reports

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article describes how you can reference the parameters of a report's RecordSource query. 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 files, RptSampl.exe (for Microsoft Access for Windows 95 version 7.0) and RptSmp97.exe (for Microsoft Access 97). For information about how to obtain these sample files, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q145777
   TITLE     : ACC95: Microsoft Access Sample Reports Available on MSL

   ARTICLE-ID: Q175072
   TITLE     : ACC97: Microsoft Access 97 Sample Reports Available on MSL

MORE INFORMATION

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

1. Open the sample database Northwind.mdb (or NWIND.mdb for in Microsoft Access 2.0) 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.

   NOTE: In Microsoft Access 2.0, there is a space in the Order ID, Order Date, and Product Name fields.

2. On the Query menu, click Parameters. In the Query Parameters box, under Parameters, type "Enter Start Date" (without the quotation marks), and under Data Type enter "Date/time" (without the quotation marks). Press TAB to move to the next Parameter field. Under Parameters, type "Enter End Date" (without the quotation marks) and under Data Type, enter "Date/time" (without the quotation marks). 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 windows, click the Reports tab, and then click New. Select the ProductReport query, then select AutoReport: Columnar. (In Microsoft Access 2.0 select the ProductReport query, click the Report Wizard Button, and then Select AutoReport.)

6. 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.

7. Add a text box to the report's header section.

8. Set the text box's ControlSource property to the following expression.

   NOTE: In the following expression, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this expression.

         ="Report Period From" & " " & [Enter Start Date] & " " & "Through" _
              & " " & [Enter End Date]
   
   

9. On the View menu, click Print Preview. When prompted for the Start Date, enter 08/08/94. When prompted for the End Date, enter 01/1/95. Note that when viewed in Print Preview, the report header now has the text, "Report Period From 8/1/94 Through 1/1/95."

REFERENCES

For more information about referencing query parameters, search for "referencing, objects and properties in expressions," and then "Refer to the value of a control or property in an expression," using the Microsoft Access 7.0 Help Index.

Keywords          : kbusage
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto