A common technique that I use is to create a form to ask for criteria for a report. One example would be a report that is used to show data for a particular time period.

(article continues after sponsor spot)

(p.s. see below for a technique to use on the report heading)

1. Create a query to feed data to the report. Add any necessary parameters (like the "between [start_date] and [end_date]" style) and test.
2. Create the report, using the query from step 1 as the record source. Test the report thoroughly. Obviously, you will have to answer the parameter questions manually, one at a time.
3. Create a form with a text box for the starting date and another text box for the ending date. Use the button wizard to add a button that will preview the report created in step 2.
4. Change the parameters in the query to this style: between [forms]![myform]![txtStartDate] and [forms]![myform]![txtEndDate]

That's it. And, no VBA coding was required.

(you may need to put some code behind the button that will verify the values in the various fields before starting the report, this would require VBA)

Creating the Heading

Since this report is for a particular time frame, we need to indicate that on the report.

1. Create a text box in the report or page header.
2. In the control source for the text box put the following:
   ="For the period of " & [Forms]![myform]![txtStartDate] & " to " & [Forms]![myform]![txtEndDate]
   If you want it formatted:
   ="For the period of " & format([Forms]![myform]![txtStartDate],"mmmm d, yyyy") & " to " & format([Forms]![myform]![txtEndDate],"mmmm d, yyyy")

Other tips related to this one are Query Parameters Part 1, Query Parameters Part 2, and Query Parameters Part 3

Further Ideas

This method requires a separate form for each report, in order to customize the collection of the criteria. Part 2 of this article will look at a method of having a single form service many separate reports. It is a method that both I and my clients appreciate. In order to know when part 2 is complete, please either visit the What's New page frequently, or better yet, subscribe to our announcement service.

Happy Coding

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

© 2006, 2007 Richard W. Killey. All Rights Reserved.