| the Manx Man's MS Access Pages
NOTE: This article is now on our
new website at
www.accessdatabasetips.com/criteria-for-a-report-1.html
Using a Form to filter data for a Report (004)
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. |
| 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)
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") |
Other tips related to this one are 1, 17, and 43.
|