Formatting Tips and Tricks

The following list details a few tricks for making nicely formatted controls for your reports. Most of them involve setting report, section, or control properties on the Property sheet:

• Printing calculations: Print a calculated field - a field decided by an expression - the same way you display one on a form: Create a text box and enter an expression in the Control Source property. Be sure to set the control's Format property, too.
• Prompting for information to print: Just as Access can prompt for information when running a query, you can use parameters when printing a report. Parameters allow you to specify information - usually in the Report or Page Header or Footer sections - that you want to print. Create a text box control where you want the information to print. For the Control Source property of the text box, enter the parameter prompt in square brackets, so it looks something like the following:
  [Enter title line]
• Avoiding space between fields: When you display several fields in a row, you may not want to leave gaps between them. For example, in a mailing label or form letter, you may want to print fields containing first names and last names with only one space between them. To eliminate extra space between fields, regardless of the length of the values in the fields, concatenate them (glue them together) using the & operator. Create a textbox control and type an expression in its Control Source property, such as the following expression:
  = [First Name] & " " & [Last Name]
  This expression glues the first name, a space, and the last name together. If the first name were Elvis and the last name were Presley, you end up with Elvis Presley (the name, anyway).
• Using conditional calculations: You can print one thing in some circumstances and another thing in others by using the iif() function. For example, you may make a report that can print either an invoice or a receipt, depending on whether the customer has paid. At the top, you include a text box with an expression in the Control Source property that spells out that Access should print either an invoice or a receipt, depending on the value of the Paid field. That expression looks something like the following:
  = iif([Paid], "Receipt", "Invoice")
• Calculating a running sum: You can tell Access to sum the values of a numeric field, showing the total of the current record (a running sum). Set the Running Sum property of the textbox control displaying that field to Yes. You may want to include two text box controls for the numeric field: one to show the value for the current record (with the Running Sum property set to No), and one to show the running sum (with the Running Sum property set to Yes).
• Hiding duplicate values: If a group of records have the same value for a control, and you want the value to print only the first time it appears, you can set the Hide Duplicates property of the field to the Yes setting. This setting is especially useful in tabular reports, in which each field appears in a separate column.

Don't use a field name as the control name for a calculated control. When you create controls, Access names them automatically, although you can change the names later. If you rename a calculated control, make sure that the name you assign isn't the same as any field mentioned in the expression (or any field in the record source of the report). Access gets confused about whether references to that name are to the field or to the control and the report displays the #Error message.