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