Tips and Tricks for working with Microsoft Access Reports

Manually Adding Grouping
1. Create report and click on View | Sorting and Grouping
2. Select field/s you want to group on Keep together property-
Yes – keeps together
With First Detail – keeps together and first record is kept with group header

Printing Data in Columns
File | PageSetup [Columns]

Printing Data in Columns with a Heading that Spans Columns
1. Create a report showing the data in columns and delete the page and report headers and footers.
2. Save the report.
3. Create another report with a header that spans the new report
4. Insert the other form as a subreport into the detail section of this report and resize so that it fills the width of the report.
5. Format borders as required
6. Save the report.

Numbering Records in a Report
1. Add an unbound text box to the detail section of the report
2. In the properties for the text box, click on the data tab
3. In the Control source property enter =1
4. In the Running Sum property select Over all or Over Group if the numbering needs to restart in each group.

Starting Page Numbering on a number other 1
1. In the footer add an unbound text box
2. Type the following into the text box =[Page]+the number you want to start on -1

Include a Running Total
1. Insert an unbound text box
2. In properties [Data] Control Source property choose the name of the field you want a running total for
3. Choose Over All or Over Group in the Running Sum property

Grand Totalling a Calculated Field
1. Use the Sum() function in the Report Footer (not Page Footer)
2. Use the same formula in the calculated field eg =sum([Fieldname]*[Fieldname])

Set Default Print Margins
Tools | Options [General]

Create a Page Break after Each Group
Add a page break in the Group Footer
OR
1. Select the Group Footer and open Properties [Format]
2. Set Force New Page property to After Section

Display Parameter Values in a Report
1. Create the parameter query and include the parameters as fields in the QBE grid – use exactly the same names eg [Start Date] as a parameter, would be something like Start: [Start Date] as a field
2. Create the report and use the fields wherever appropriate in the report, eg form report title control source may read:
="Customer Orders between" & " " & [Start Date] & " " & [End Date]

Open Report Based on a Single Record Shown in a Form
1. Create a report as usual
2. Create a macro:
Action: Open Report
Report Name: Name of Report
Where condition: [fieldname] = Forms![formname]![formcontrolname]

Fieldname is the unique reference that identifies the record

3. Drag the macro onto the form

Empty Calculated Fields
If some records show empty calculated fields this is because the calculated field value is Null. To covert Nulls to zeros use the NZ() function.
eg =NZ([Price])*.2

Column Headings in SubReports
Page headers and footers will not print – put headings in report header.
If the subreport goes over one page but headings in a group header.

Calculations in PageHeaders/Footers
Sum() does not work in a Page Header, therefore create the calculation elsewhere, such as the report footer and the create a text box that refers to that control in the page footer =[ControlName]