• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 575
  • Last Modified:

Including sort group information on report page headers

I am creating a multi-column access report. It looks like a phone book, with names grouped by the first letter of the last name (e.g. A, B, C, etc.).  Is there a way to show either the sort grouping fields in the report header (in this case A, B, C), so that a reader can tell that a page goes from the "A" names to the "C" names, and the next page goes from the "D" names to the "X" names?  Or, even show the first and last lines on the page (remember it's multi-column) in the report header...The header would then say something like "Abrams" on the left side of the page, and "Epsen" on the right side of the page.

Thanks for your help...Tom
  • 2
  • 2
1 Solution
You could try using Min(Name) and Max(Name) in the Page header.

If that doesn't work you could create a group for a given number of names using the GroupInterval property and use Min() and Max() in the Group Header

Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
 Page footer would be easy, but the page header is a bit tougher.  Your going to need to use two-pass printing, which you activate by using the .Pages property somewhere on the report (ie. a hidden control).

  With two-pass printing, the report engine will make one complete pass through the report in order to get the total pages.  On this pass, you'll add code to pickup the last name for each page and store it in an arrray or table.

  On the second pass, when the report actually prints, you'll have the first name available and the last name on the page from the array.

  This technique is great for all types of things, like doing page numbers over groups, table of contents, etc.  

  Here's a link to a MSKB article which shows you how to store using a table:


  In your case however, your going to store the page number and the name.  Then on the print pass, you'll lookup the page number and retrive the name that will appear at the bottom.

  Let me know if you need more specifics.

Jeffrey CoachmanMIS LiasonCommented:

Great question!

I made a sample "Phone book" style Report years ago for a student.

And yes!, it would be useful to have the "Name Range" on the page.

The bad new is that, as JDettman states, it is easier in the page footer than in the Header.

Here we go...

Create a text box in the Page FOOTER.
Name this textbox: txtLastLastNameOnPage
Set it "ConTrolSource" Property to your last Name Field
Something like: =[LastName]
Move this textbox to the right hand side of the Page FOOTER
It will display the last name on the page.
Easy right?

To get the "First" name requires a bit more work.
Create another text box in the Page HEADER.
Name this new textbox: txtHeaderLastName
Set its controlSource to: =[LastName]
You guessed it, This will display the "First" LastName on the page
But we don't need to see it so...
Set its "Visible" property to: NO

Now, create one more new textbox in the Page FOOTER.
Name it: txtFirstLastNameOnPage
You will not need to set the ControlSource Property for this textbox.
Move it to the bottom Right side of the Page FOOTER
Set its "TextAlign" Property to: Right

Now Click: View-->Code, from the menu, and paste this in:

Private strPageBeginningLastName As String

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
    'Set the Private Variable to the "First" Last Name on the page.
    strPageBeginningLastName = Me.txtHeaderLastName
End Sub

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
    'Insert the Variable into the txtFirstLastNameOnPage textbox.
    Me.txtFirstLastNameOnPage = strPageBeginningLastName
End Sub

Save your report then Preview it.
This should work for you.

I have created a sample.
Here it is:
Have fun!

And thanks again for an interesting and thought provoking question!

Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Save your report then Preview it.
This should work for you.>>


 Just one gotcha; he wanted the range in the header, not the footer.  As you have shown, placing the rang ein the footer is very straight forward.  Just save the name in a variable in the header event and poke it into a control when the footer occurs.

  Having the range in the header however requires a totally different technique.

Jeffrey CoachmanMIS LiasonCommented:

< Just one gotcha; he wanted the range in the header, not the footer>

I mentioned that in the first part of my post.

My thought was that if they saw how to do it in the footer, they might be OK.

The reason I posted was that, as I said, I had been using a similar report for years, and I never thought about putting the "Name Range" in the footer.

So it was a Learning experience for me as well!
(If you read the post, you will notice by the way it is written, that I was actually DOING it as I was typing!


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now