Microsoft Access: Reports

Provided by Allen Browne.  Created July 2008.  Updated October 2009.

Duplex reports: start groups on an odd page

To print invoices on a printer that supports double-siding, you need each one to start on an odd page. Otherwise Fred's invoice begins on the back of Betty's.

The solution is to add an extra group footer, with the Force New Page property set to Yes. Then hide this section if the next group will already begin on an odd page.

Download the sample database (26KB zipped, for Access 2000 and later.)

(Note: In Access 2007 and later, you must use Print or Print Preview for this to work. The events don't fire in Report view or Layout view.)

Using a field twice in Sorting and Grouping

In the screenshot below, the OrderID field appears on two rows. In both cases, we chose Yes for Group Header and Group Footer. This gives us two headers and two footers for OrderID.

The inner footer (the one nearest the Detail section) contains the totals for the order. Its Force New Page property is set to "After Section", so the next section to print will start on a new page.

The outer footer also has Force New Page set to "After Section", so the next section (the header for the next order) starts on another new page. This gives us a blank page between orders.

But if we are already on an odd page, we need to suppress the blank page. We do that by setting its Visible property to No in its Format event.

Report screenshot

The code

The outer OrderID Footer's Format event code looks like this:

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
    Dim bIsOdd As Boolean
    bIsOdd = (Me.Page Mod -2)   'Yields 0 for even, or -1 for odd.
    'Hide this Section (and its page break) if already at odd page.
    With Me.Section("GroupFooter1")
        If .Visible = bIsOdd Then
            .Visible = Not bIsOdd
        End If
    End With
End Sub

The Mod operator gives the remainder after division. Mod -2 yields 0 for even pages, or -1 for odd pages. Since 0 is False, and -1 is True, we set the section's Visible property to the opposite.

So, the section (and its page break) is printed if the page is even, but suppressed if we are already on an odd page.

The code toggles the Visible property only if needed, since setting a property is slower than reading it.

Other tricks

For an order that runs onto multiple pages, we want the column headings (Quantity, Product, etc) repeated. The sample report achieves that by setting the Repeat Section property of the inner group header to Yes.

The address panel illustrates how to suppress blank lines by careful use of the two concatenation operators in Access:

Say the Address2 field is Null. Using plus to concatenate the carriage return and line feed (characters 13 and 10), that entire line becomes null. Concatenating the result with ampersand means that the entire expression still works without that line.

In some cases, Access gives errors if you refer to a field that does not have a text box on the report. We therefore placed the Tax box in the Detail section (hidden), to avoid the error when summing it.

The sample database stores the TaxRate in the [Order Details] table. This suggestion copes with countries where different products can have different tax rates. For example, essential food items are tax-exempt in some countries, while other items on the same invoice (such as processed food) include tax.

Home Index of tips Top