---

MS Access 2003: Force a page break based on the first letter of the LastName field in a report

This MSAccess tutorial explains how to force a page break based on the first letter of the a field in a report in Access 2003 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2003/XP/2000/97, I have a database and I'd like to insert a page break in a report listing names and addresses. For example: page break after all the last names that start with the letter A and so forth.

Answer: Let's look at a report to demonstrate.

In our report, we are going to force a page break based on the first letter of the LastName field. So after all LastNames beginning with A, a page break will occur. After all LastNames beginning with B, a page break will occur, and so on.

First, we need to open our Report in Design view.

Under the View menu, select Properties. When the Properties window appears for the Report, select the Record Source property. A button with three dots to the right of the Record Source property should appear. Click on this button.

The Query Builder window should appear.

Next, you want to parse out the first letter of the LastName. This can be done with the following:

LastLetter: Mid([lastname],1,1)

Now your query should return a field called LastLetter which is the first letter of the LastName.

Close down the Query Builder and when prompted "Do you want to save the changes made to the SQL statement and update the property", click on the Yes button.

Now when you return to the Report, click on the "Sorting and Grouping" button in the toolbar.

The Sorting and Grouping window should appear.

Select LastLetter in the Field/Expression column. Set the "Group Footer" property to "Yes".

You can also choose to sort your report by other criteria. In this example, we've chosen to sort the report results by LastLetter, then LastName, and then FirstName.

Now when you view your Report, you should see a section for LastLetter Footer. We've shrunk the footer so that the height of it is 0.

Next, highlight the LastLetter Footer bar. The Properties window should now display the properties for the LastLetter Footer.

Set the "Force New Page" property to "After Section".

Now when we run the report, a new page should be forced after the first letter of the LastName field. So a page break will occur after all LastNames starting with A have been listed, then after all LastNames starting with B....and so on.