Sub-Subreport Totalling 1

[corner40]

I have a report that has a sub report "sbrpt-department billing details" that is made up of four sub-subreports 1)sbrpt-department billing details beverage 2)sbrpt-department billing details consumable 3) sbrpt-department billing details food 4)sbrpt-department billing details sundries...
I have a total on each sub-subreport but I would like a grand total on the subreport "sbrpt-department billing details"...the problem is that sometime one of the sub-subreports doens't have anything in it and the total produces the traditional #error and this in turn screws up my grand total because you can't add an #error...
my sub-subreports are pulled from queries...
any help would be greatly appreciated...
I have tried the nz function and tried to set the defaults to 0 but you can't do that in reports...
any help would be terrific
thanks
Jeremy

 

[corner40]

I've scoured the board before and after having posted this and still no solution...any help would be great
thanks
jeremy

 

[Dustman]

Hmm.. I was going to suggest the nz function too.. maybe you should try putting the nz function in the query instead of in the report. I don't know that it will work but thats what I would try. -Dustin
Rom 8:28

 

[FGaumond]

Maybe try to put the NZ function in your queries.

You coul also try to use the IIF function.

 

[Dalain]

Try creating a hidden field on the main report [CalTotal1] and make the Controlsorce a DSum Value (Which would given you the same value as the subform then use it to create your total on your main form.

For example Subform1 RecordSource would be "SELECT * FROM BankDeposits"

Your hidden [CalTotal1] feld's ControlSource = DSum("[BankDeposit]","BankTransQuery&quot

[CalTotal1] will now return a null value or An Actual Number. (if there are no records, the DSum function returns a Null)

You now create your SubRptTotol1 field. I normaly place it visible below each of the subreports.

SubRptTotol1 controlsource =IIf(IsNull([[CalTotal1]),0,[[CalTotal1])

This will return a 0 if the [CalTotal1] is null or an actual value.

Grand Total = SubRptTotol1 + SubRptTotol2 +SubRptTotol3

Hope this Helps
Pierre

 

[corner40]

Hi Pierre
thanks for the responses everyone. Pierre yours seems to be along the lines I'm looking for but I was unable to get it to work properly. I'll give you a little bit more detail that might help you help me, if you don't mind. I'll focus on the layout of one report and how it ties into the others.
This report is named "sbrpt-Department Billing Details Consumables". The fields on the report are OrderID, Item#, Product_Descript, Brand, Size, Unit_Price, Quantity, Subtotal(which is [Unit_Price]*[Quantity]...not part of the query), totalsubform(which is =Sum(nz([UnitPrice]*[Quantity]))...not part of the query) then in the Report footer I have the total which is = to totalsubform...
the main report is just a report with the four subreports on it and a total at the bottom…I know what you mean about having the hidden field that does some calculations and then saying the total field is just equal to it…that’s what I usually do as it’s the only way I can get it to work properly…the report is run by the query “q-Department Billing Details Consumables”…it has only the fields I stated above in it…I hope this helps a little bit more…thanks in advance for the help
jeremy

 

[Dalain]

What I have done her is try to use your FieldNmaes. In place of mine own. I still don’t know you’re your RecordSource is for you sub reports and what field(s) you are Linking on “SubReport Prperties - Link Master Fields/Link Child Fields

Assuming you have the Following:

Report Name = sbrpt

Fields in the Main Report (Also Assuming you have the same Fields in Subreports)
OrderID
Item#
Product_Descript
Brand
Size
Unit_Price
Quantity
Subtotal = [Unit_Price]*[Quantity]
Totalsubform = Sum(nz([UnitPrice]*[Quantity]))

Is this field available in the Sub Report?
Subtotal = [Unit_Price]*[Quantity]

It should be. Also you should go into the Table Design and Set the Unit_Price and Quantity fields’s Default Value to 0 and Required Value to Yes . This way you don’t have to worry about recieving a Null Value.

Create Four(4) hiddenfields on the Main ReportForm.

SubFrmTotalNotVis1.ControlSource = DSum( "[Unit_Price ]","[TableNameForSubform1]", "OrderID = " & OrderID)
SubFrmTotalNotVis2.ControlSource = DSum( "[Unit_Price ]","[TableNameForSubform2]", "OrderID = " & OrderID )
SubFrmTotalNotVis3.ControlSource = DSum( "[Unit_Price ]","[TableNameForSubform3]", "OrderID = " & OrderID )
SubFrmTotalNotVis4.ControlSource = DSum( "[Unit_Price ]","[TableNameForSubform4]", "OrderID = " & OrderID)


TableNameForSubform1 is the Name of the Table or Query that you have entered as the RecordSource for that subReport.

“OrderID = “ & OrderID is a filter that specifies which records to Sum. This in normally the Unique IDNumber/Name of a field in the Main Report. It is would also be the Field used to List the SubReport information.

IF OrderID is a text field the Above code should be "OrderID = '" & OrderID & "'"

In the Main Report Under each SubReport Create a SubReport’s TotalField (You could make this field not visible as well.

SubRptTotol1.ControlSource =IIf(IsNull([[SubTotalNotVis1]),0,[[ SubTotalNotVis1]) (This would replace your Field – Totalsubform = Sum(nz([UnitPrice]*[Quantity]))

And the
ReportGrandTotal = SubRptTotol1 + SubRptTotol2 + SubRptTotol3 + SubRptTotol4

This is more or less a repeat of my last post. I hope using more of your Field Name might help you spot the error.


Hope this helps

Pierre