Designing Reports   «Prev  Next»
Lesson 11 Creating a calculated control
Objective Create a calculated control in Report Design view.

Creating Access Calculated Control

The best way to create a calculated control, especially if you want to aggregate data, is to use the Report Wizard. Even when the Report Wizard doesn’t give you exactly what you want, it’s far easier to edit a calculated control than to create one from scratch.
That said, however, it is possible to create calculated controls--in fact, the process is identical to creating a calculated control in a form: create the text box and type the expression into it.
One difference from a form is that an aggregate calculation is much easier to create on a report. Expressions for aggregate expressions need to be put in the correct place on the report. If you are aggregating all the data for one company, for instance, the correct place to put the expression is in the Company header or footer.
Access uses the same common functions used by Excel. The simple ones that you might want to use are:
  1. Avg, Sum, Count, Min, and
  2. Max.
Access has many more functions than these, and they’re all listed in the Help system.
When you write an expression, do not forget the special characters that Access needs: notably, field names must be enclosed in square brackets.

View the table below to review the chart of special characters.
Special Characters
This type of data Looks like this in an expression
Text “text”
Date #1/1/00#
Time #1:00am#
Number 10
Field Name [field name]

Learn to add page breaks your report, in the next lesson.

Creating Calculated Control - Exercise

Add calculated controls to the Monthly Hours by Project report in this exercise.
Creating Calculated Control - Exercise