Group Sum Calculation Works on First Set of Data Only (1 Viewer)

manybeats · Aug 28, 2012

Hi! I have created a report with 4 subreports located in the detail section of the mainform. Each subreport has a sum in the report footer. These sums are working. On the mainform detail section, I added a textbox which adds the sums from the 4 subreports. After the first grouping, the sum shows up correctly. But each grouping after that shows a "#Type!" error. I believe this error is happening because of null values in some of the subreports. So, I added a Nz function to the sum totals in each subreport but am still getting the error.

I'm not sure if I am using the Nz function incorrectly or if it is something else altogether. I've attached my db - look in rptInvoice. Thanks for any help you can give me.

vbaInet · Aug 28, 2012

What fields should we be concentrating on? What values do you expect to see?

manybeats · Aug 28, 2012

On the mainform of rptInvoice, txtTotalCELPA is the calculated total field that is not working. It works correctly on the first set of data, but all instances of it afterwards come up with the error.

In the subreports, the sum fields where I've used Nz are in the report footer and are called:
txtTotalTimesheet
txtExpenseAmount
txtEquipConsAmount
txtConInvAmount

Thanks!

vbaInet · Aug 29, 2012

I made some changes to sort out the problem that occurs when the subreport has no corresponding value to the parent report and this works in Print Preview, but somehow it doesn't work in Report View. Well, it partially works in the sense that, for the second group if you scroll to the very top of the group it will work but as soon as you scroll further down it doesn't work.

It seems as if it's losing reference to the control on the subreports at that point. My gut instinct would be to recreate the subreports but of course it will take some time to build.

If you don't want to go down that route, you can just use DSum() instead. I've attached the db with the changes I made so you can have a look.

manybeats · Aug 29, 2012

I have some family visiting, but will take a look at your changes in the next few days. Thanks so much for your help - I really appreciate your time.

I have read other posts about problems between report view and print preview, so it's not isolated to this database. I have not found a solution unfortunately. As I move through finalizing things, I can make the report always open in print preview and it shouldn't be an issue. At least I think I can do that!

vbaInet · Aug 29, 2012

Let us know how it goes.

manybeats · Aug 31, 2012

So I used what you did and got everything working (I am working in Access 2010, so copied and pasted the nnz module and the changes to the sum formula). Thanks so much!

Now.... I am trying to get the grand total with no luck. Basically, I copied the same total formula from the detail section into the group footer and the report footer (thought if it was located in one of those it would know to get data from all the groupings) and all I am getting back is the last group's total, not the sum of all the totals. Any chance you could steer me in the right direction on that one?

vbaInet · Aug 31, 2012

Any aggregate function (such as Sum, Count etc) you place in a group footer or header will calculate for that group alone. If you placed an aggregate function in the Report footer it will aggregate for the entire report.

manybeats · Sep 5, 2012

I've tried placing the total in the report footer (basically the same expression as I have in the detail section, which adds up each sum from the subreport footers), but when I view the report, it only gives me the sum from the last grouping, not all the groupings.

------------------

I've figured it out! I added a running sum for each subreport and then added those fields together in my report footer. Thanks again for your help!

vbaInet · Sep 5, 2012

Pleased to hear manybeats.

Yianni · Aug 1, 2017

I have a similar problem and hope you can help. I have a report which counts and calculates properly. After I add it to another report as a subreport the counts per Town are correct but the percent calculation is done on the grouping TOWN. countinTown/totalcount

Can you show me how to fix it so my calculation (percent) is calculating correctly

Thanks

Group Sum Calculation Works on First Set of Data Only (1 Viewer)

manybeats

Registered User.

Attachments

vbaInet

AWF VIP

manybeats

Registered User.

vbaInet

AWF VIP

Attachments

manybeats

Registered User.

vbaInet

AWF VIP

manybeats

Registered User.

vbaInet

AWF VIP

manybeats

Registered User.

vbaInet

AWF VIP

Yianni

Registered User.

Similar threads

Users who are viewing this thread