[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

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


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?

1 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.

PhysimedAuthor Commented:
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.


Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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