Report with subreport totals. How to add into a report total (1 Viewer)

mafhobb

Registered User.
Local time
Today, 03:49
Joined
Feb 28, 2006
Messages
1,245
I have a report (rptInvoice) with a number of subreports (MaterialsDoneSubrpt, MaintenanceDoneSubrpt, CleaningsDoneSubrpt). Each of these subreports has a subtotal text field where all the costs associated with that subreport are added (Sum([....])
Now I need to add up all these subtotals into a single total in a textbox on the main report.
How do I do this?
mafhobb
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:49
Joined
May 7, 2009
Messages
19,170
Control source of textbox in Main Report:

=Nz([MaterialsDoneSubrpt]![theTotalTextbox] , 0) + Nz( [CleaningsDoneSubrpt]![theTotalTextbox], 0)
 

mafhobb

Registered User.
Local time
Today, 03:49
Joined
Feb 28, 2006
Messages
1,245
I have entered the rowsource:
Code:
=Nz([rptMaterialsDoneSubrpt]![txtMaterialsSubtotal] , 0) + Nz( [rptCleaningsDoneSubrpt]![txtCleaningsSubtotal], 0) + Nz( [rptMaintenanceDoneSubrpt]![txtMaintenanceSubtotal], 0) + Nz( [rptReservationsDoneSubrpt]![txtReservationsSubtotal], 0)
But the output in my box is : #Size!
I have tried to enter the value for just one subtotal:
Code:
=Nz([rptMaterialsDoneSubrpt]![txtMaterialsSubtotal] , 0)
But i get the same result: #size!
mafhobb
 

mafhobb

Registered User.
Local time
Today, 03:49
Joined
Feb 28, 2006
Messages
1,245
Does anyone have a suggestion?

I have my report almost done and the subreports are all working perfectly. How do I add the subtotals in those subreports so the total shows up on my report?

Can textboxes be manipulated like that on a report?

mafhobb
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:49
Joined
May 7, 2009
Messages
19,170
try removing Nz(), what do you get?
 

mafhobb

Registered User.
Local time
Today, 03:49
Joined
Feb 28, 2006
Messages
1,245
OK, so I have been investigating a bit further and this is what I have found:

If I enter this as a source for the report field, the field shows the addition of the two subreports correctly:

"=Val(Nz([Report]![rptMaintenanceDoneSubrpt]![txtMaintenanceSubtotal].[Value],0))+Val(NZ([Report]![rptMaterialsDoneSubrpt]![txtMaterialsSubtotal].[Value],0))"
Result = 220.09E

If I add another subreport to it (see below), then I get "#Type!"

"=Val(Nz([Report]![rptMaintenanceDoneSubrpt]![txtMaintenanceSubtotal].[Value],0))+Val(NZ([Report]![rptMaterialsDoneSubrpt]![txtMaterialsSubtotal].[Value],0)) +Val(NZ([Report]![rptReservationsDoneSubrpt]![txtReservationsSubtotal].[Value],0))"

As fas as I can see, the only difference is that the value of txtReservationsSubtotal is zero, "0". Not null, but zero; at least that is what is displayed on the report.

Removing the Nz function does not make a difference.

mafhobb
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:49
Joined
May 7, 2009
Messages
19,170
so there is really a Value, but is very small:

0.0000000022

on your subreport use Round([theTotal, 2))
so that you will only get 2 decimal points.
 

mafhobb

Registered User.
Local time
Today, 03:49
Joined
Feb 28, 2006
Messages
1,245
so there is really a Value, but is very small:

0.0000000022

on your subreport use Round([theTotal, 2))
so that you will only get 2 decimal points.
My Rowsource for that field (txtReservationsSubtotal) in the subreport (rptReservationsDoneSubrpt) is now:
=Round(Sum([Comission]),2)
The value shown on the screen is 0.00, which is correct.

The rowsource for the extended total on the report is:
=Val(Nz([Report]![rptMaintenanceDoneSubrpt]![txtMaintenanceSubtotal].[Value],0))+Val(Nz([Report]![rptMaterialsDoneSubrpt]![txtMaterialsSubtotal].[Value],0))+Val(Nz([Report]![rptReservationsDoneSubrpt]![txtReservationsSubtotal].[Value],0))

Still, when the value of [txtReservationsSubtotal] is added to the other two, I get "#Type!"

mafhobb
 
Last edited:

mafhobb

Registered User.
Local time
Today, 03:49
Joined
Feb 28, 2006
Messages
1,245
I have now added some data to the underlaying table so that the query that populates the subreport that txtReservationsSubtotals is calculated upon would have a value different than zero. When I do that, the Extended total field shows the correct result, that is, the error goes away.

So, I think that this has to do with the fact that the query that populates the subreport may return no values, thus causing the subreport running sum txtReservationsSubtotal =Round(Sum([Comission]),2) to possibly display "0.00" on the report itself, but in fact have no value at all.

However, to test that, I have tried unsuccessfully to change the txtReservationsSubtotals rowsource to
=Nz(Round(Sum([Comission]),2),0)
and then to
=Nz(Val(Round(Sum([Comission]),2)),0)

Sooooo.....I am really out of ideas at this point and willing to try anything.

mafhobb
 
Last edited:

mafhobb

Registered User.
Local time
Today, 03:49
Joined
Feb 28, 2006
Messages
1,245
...shameless bump to see if anyone has a suggestion...

mafhobb
 

Users who are viewing this thread

Top Bottom