Allow users to run their own Access reports from parameter queries

Why re-invent the wheel? Here's a way to make consistent Access reports from similar data, while allowing your users to set their own data parameters.

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.

Editor's Picks