Allow users to run their own Access reports from parameter queries

Do you often find yourself creating the same Access report
over and over again and only changing the dates? If so, you can save time by
creating a report from a parameter query that lets users specify the dates for
the report.

For example, the Human Resources department requires an
annual report that lists the employees scheduled for vesting in the company
pension plan during the coming year. You’ve already created a Vested_Year query that calculates the vesting date for each
employee. Follow these steps to customize your previously created report:

1. Open
   the Employees Database and click Queries under objects.
2. Click
   New.
3. Select
   Design View and click OK.
4. On the
   Queries tab, double-click the Vested_Year query
   and click Close.
5. In the
   Field Windows, double-click Employee ID, Last_Name,
   First_Name, Hire_date,
   and Vested_date.
6. In the
   criteria cell under the Hire_date field enter:
   Between [Please enter beginning date] AND
   [Please enter ending date]
7. Close
   and save the query.
8. In the
   database window under Objects, click Reports.
9. Click
   New, and then click Create Report by using the wizard.
10. Under
    Tables/Queries, select Query:Vested_Year.
11. Click
    the double arrow button to select all fields and click Next.
12. Select
    Hire_date, then Last_Name,
    then First_Name, and then click Next.
13. Click Next three times.
14. Enter Vested
    Dates For Employees Hired As Of in the Report
    Title text box.
15. Select
    the Modify the report’s design radio button and click Finish.
16. Click
    the Text Box Tool in the Report toolbox.
17. Click
    and drag to locate a text box next to As Of in
    the report title.
18. Enter
    the following formula in the text box:
    =Reports![Vested dates for employees hired
    as of]![Enter beginning hire date]
19. Make
    any necessary format changes and run the report.

Your users will now be able to enter a beginning and ending
date to customize their own reports.

Miss a tip?

Check out the Microsoft Access archive, and catch up on our most recent Access tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.