MS Access, VBA : How to use the SUM function on Unbound field in a report?

Posted on 2006-05-25
Last Modified: 2008-02-01

This problem has been haunting us for a while. Let me explain the scenario:

We are doing a REPORT that will calculate the total time use for a job in a month.

January     30 min
February   45 min
Total         75 min

Each month time is populated through VBA code on the ON FORMAT of the section. (searching the right compagny, the right employee and other criterias)

Then when we try to use the SUM function on the control source of the sumOfTemp field nothing gets calculated.

We do not have problem using the SUM function when our report is populated dirrectly through a table.

Do you have any ideas on how we can make this work?

Question by:Physimed
    LVL 58

    Accepted Solution

    Hello Physimed

    The Sum() function is in fact an SQL aggregate function – used in queries. When you use that, the report generator creates a query to fill the relevant controls in group headers/footers and the report header/footer.

    Therefore, you can only use field names inside of the Sum(), nothing else.

    Since you already have the code to fill the detail's figures from VB, adapt it so that it can also fill the group totals and the grand total.


    Author Comment

    Thank you harfang!

    At least we know that it  can't be done with the SUM function.

    We have finished our report and our sum are now calculated through VBA Code.

    Thanks again.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

    760 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

    8 Experts available now in Live!

    Get 1:1 Help Now