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:
- Open
the Employees Database and click Queries under objects. - Click
New. - Select
Design View and click OK. - On the
Queries tab, double-click the Vested_Year query
and click Close. - In the
Field Windows, double-click Employee ID, Last_Name,
First_Name, Hire_date,
and Vested_date. - In the
criteria cell under the Hire_date field enter:
Between [Please enter beginning date] AND
[Please enter ending date] - Close
and save the query. - In the
database window under Objects, click Reports. - Click
New, and then click Create Report by using the wizard. - Under
Tables/Queries, select Query:Vested_Year. - Click
the double arrow button to select all fields and click Next. - Select
Hire_date, then Last_Name,
then First_Name, and then click Next. - Click Next three times.
- Enter Vested
Dates For Employees Hired As Of in the Report
Title text box. - Select
the Modify the report’s design radio button and click Finish. - Click
the Text Box Tool in the Report toolbox. - Click
and drag to locate a text box next to As Of in
the report title. - Enter
the following formula in the text box:
=Reports![Vested dates for employees hired
as of]![Enter beginning hire date] - 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.