How identify the max number in the detail section of a report and put iit in the footer of that group

Posted on 2012-08-23
Last Modified: 2012-08-24
I have a report that contains records in the detail section of the report.  In each record is a number field named "txtCount".  Then in the footer of that group I would like to have a field that has the max number from the "txtCount" number in the detail section.

In other words, if the detail records look like:

Field1          Count
Record 1     5
Record 2     7
Record 3     12
Record 4     4

Then the number I want to show in the footer is 12.

How can I do this?  What is the control source for the field in the footer?

Question by:SteveL13
    LVL 56

    Accepted Solution

    =Max([Count])  should do the trick.

    The other way is with a variable, which you use to populate a control, by:
    it by:

    1. Declaring a variable in the reports module (ie.  lngMaxCount as Long)

    2. In the Group Header's OnFormat event, set that to -1

      lngMaxCount = -1

      Note that you can have the group header print, but with a height of 0 if you don't have one currently.

    3.  In the Detail's OnFormat event, do:

       If Me![Count]> lngMaxCount then lngMaxCount = Me![Count]

    4. Finially, in the Groups footer OnFormat event to:

         Me![<myControlName>] = lngMaxCount

    LVL 77

    Expert Comment

    Just add a textbox into the report footer with a controlsource of


    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now