DatabaseLessons.com

'Serving the Microsoft® Access
Community since 1997'

News/Blog
Tables
Queries
Forms
Reports
Modules
Miscellaneous
Subscribe
Free Samples
Videos
Services
Links
About Us

 

 

Using a Form to Collect Criteria for a Report

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.

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

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

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

(article continues after sponsor spot)



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

Step 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 - you could also use a combo box to collect data for the report criteria)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Creating the Heading

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

Step 1

Create a text box in the report or page header.

Step 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")

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, 2008 Richard W. Killey. All Rights Reserved. - Privacy Policy