Microsoft Access 2010 Bible

Chapter 20: Advanced Access Report Techniques

749

The Count() and Sum() functions both require information that isn’t available until the entire

report has been processed in the first pass. As long as Access can find the arguments provided to

these functions (CompanyName and Purchases) in the underlying recordset, the calculations

proceed without any action by the user.

FIGURE 20.42

rptSummary in Design view

Assigning unique names to controls

If you use the Report Wizard or drag fields from the Field List when designing your reports, Access

assigns the new text boxes the same names as the fields in the recordset underlying the report. For

example, if you drag a field named Discount from the field list, both the Name and

ControlSource properties of the text box are set to Discount.

If another control on the report references the text box, or if you change the ControlSource of

the text box to a calculated field, such as

=IIf([Discount]=0,”N/A”,[Discount])

you’ll see #Error when you view the report. This happens because Access can’t distinguish

between the control named Discount and the field in the underlying recordset named

Discount.

You must change the Name property of the control to something like txtDiscount so that

Access can tell the difference between the control’s name and the underlying field.