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?

Who is Participating?
harfangConnect With a Mentor Commented:
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.