Calculating values from subreports when one or more is null

[CindySue]

I found something similar, but can't get it to work--maybe because I'm working in a report rather than a query?

I have a main report that has three subreports in it. Each subreport has a total for items in that subreport. If all three have entries, I can get a grand total, but if one or more doesn't, it just says error. I first tried using IIf/IsNull, but again, I get totals only when all three subreports have amounts. I then found nz, and tried that, but still I only get a total when all three have values. This seems like it just shouldn't be that complicated.

Here's what I have:

=Nz([Reports]![Final Bid Sheets]![Final Bid Sheet Auction Items subreport]![Auction Total])+Nz([Reports]![Final Bid Sheets]![Final Bid Sheet Party 1 subreport]![Party1 total])+Nz([Reports]![Final Bid Sheets]![Final Bid Sheet Party 2 subreport]![Party2 total]) 

Can anyone tell me what I'm doing wrong? Or if there is an easier way of doing this? Thanks so much.

[Rabbit]

I misunderstood the original circumstances, use iif() in conjunction with IsError()

=iif(IsError(Reports!MainReport!SubReport!Control), 0, Reports!MainReport!SubReport!Control)
 

You may or may not need to use Nz, Sum, Val in conjunction with this as well.

[CindySue]

I may be getting closer. I tried the suggestions by Rabbit and by Puppydogbuddy, but I get 0 for every single total, however,I may have discovered what part of my problem is (or not!). Auction Total is the name of the control that adds up the Winning Bid Amount field in the Auction Items subreport. Party1 total and Party2 total are the names of the controls that add up the amount field in the Party 1 and Party 2 subreports. When I substituted the names of the actual fields (Winning Bid Amount, Amount and Amount) into Rabbits code, it begins to work somewhat. It adds up the last item in each of the subreports. For example, if there are two auction items for $10 each, two Party 1 for $20 and two Party 2 for $30, the total is $60--adding up the last 10, 20 and 30. I've been trying to work the sum around my code to see if that will cause it to add all the totals, but I can't seem to get that right. Am I on track that the problem is that my code was using control names rather than field names, or was this just a wild tangent that gained me no ground?

Anyway, here's my code now:

=IIf(IsError([Reports]![Final Bid Sheets]![Final bid Sheet Auction Items subreport]![Winning Bid Amount]),0,[Reports]![Final Bid Sheets]![Final bid Sheet Auction Items subreport]![Winning Bid Amount])+IIf(IsError([Reports]![Final Bid Sheets]![Final bid Sheet Party 1 subreport]![Amount]),0,[Reports]![Final Bid Sheets]![Final bid Sheet Party 1 subreport]![Amount])+IIf(IsError([Reports]![Final Bid Sheets]![Final bid Sheet Party 2 subreport]![Amount]),0,[Reports]![Final Bid Sheets]![Final bid Sheet Party 2 subreport]![Amount])

And I really appreciate all the help! I'm doing this for a charity.

I have been trying these in all sections (detail, group footer, page footer and report footer) by the way. Is there a particular section they must go in?

[Rabbit]

Calculate the sum in a control on the subforms and then call that control from the main form.

[puppydogbuddy]

CindySue,
To answer your question, "Am I on track that the problem is that my code was using control names rather than field names, or was this just a wild tangent that gained me no ground?"

The answer is yes, you should be referring to the control source, not the control name when you are processing totals. Summing the control source picks up all the detail lines, while summing the control name picks up just the first detail. That explains why the earlier formulas you had did not work. If you plug the Control Source into the code I gave you or the code Rabbit gave you, it should work, provided that your totals are in the subreport header or footer. Totals in the headers or footers must contain the equal sign and Sum keyword. The detail lines reference the control source without use of the equal sign or Sum keyword.

Hope this helps.

[CindySue]

I'm completely confused. I guess I don't know what the control name is, then. I have a text box on the subreport that creates the total, and it shows up on in the subreport inside the main report with the correct total for all records. When I click on the right side of that control in the subreport and go to properties, under name it says Auction Total. I thought that was the Control Name. But when I substitute those names (Auction Total, Party1 total and Party2 total) in the code in place of the field names (Winning Bid Amount, Amount and Amount) I get #Error.

[puppydogbuddy]

I'm completely confused. I guess I don't know what the control name is, then. I have a text box on the subreport that creates the total, and it shows up on in the subreport inside the main report with the correct total for all records. When I click on the right side of that control in the subreport and go to properties, under name it says Auction Total. I thought that was the Control Name. But when I substitute those names (Auction Total, Party1 total and Party2 total) in the code in place of the field names (Winning Bid Amount, Amount and Amount) I get #Error.

Name is control name
Control source is the property just below the name. The control source is what you should be using in the summations

[puppydogbuddy]

Name is control name
Control source is the property just below the name. The control source is what you should be using in the summations

Let me clarify
use control source when summing a column of numbers
use name when referencing a control that has a summaton and you want to pick up the result of the calculated control.

see this link:
http://allenbrowne.com/casu-18.html

[CindySue]

So I replaced
[Auction Total] with [=sum([Winning Bid Amount])]
[Party1 Total] with [=sum(Amount)]
[Party2 Total] with [=sum(Amount)]
in my code and I get "The expression you entered contains invalid syntax."

I also tried leaving out the = sign, but got the same error. My code is now:

 
=Val(Sum(Nz(IIf(IsError([Reports]![Final Bid Sheets]![Final bid Sheet Auction Items subreport]![=sum([Winning Bid Amount])]),0,[Reports]![Final Bid Sheets]![Final bid Sheet Auction Items subreport]![=sum([Winning Bid Amount])])+IIf(IsError([Reports]![Final Bid Sheets]![Final bid Sheet Party 1 subreport]![=sum(Amount)]),0,[Reports]![Final Bid Sheets]![Final bid Sheet Party 1 subreport]![=sum(Amount)])+IIf(IsError([Reports]![Final Bid Sheets]![Final bid Sheet Party 2 subreport]![=sum(Amount)]),0,[Reports]![Final Bid Sheets]![Final bid Sheet Party 2 subreport]![=sum(Amount)])))) 
 

I'm almost ready to take an adding machine and hand write in the totals at this point. This just doesn't seem like it should be so hard.

[puppydogbuddy]

So I replaced
[Auction Total] with [=sum([Winning Bid Amount])]
[Party1 Total] with [=sum(Amount)]
[Party2 Total] with [=sum(Amount)]
in my code and I get "The expression you entered contains invalid syntax."

I also tried leaving out the = sign, but got the same error. My code is now:

 
=Val(Sum(Nz(IIf(IsError([Reports]![Final Bid Sheets]![Final bid Sheet Auction Items subreport]![=sum([Winning Bid Amount])]),0,[Reports]![Final Bid Sheets]![Final bid Sheet Auction Items subreport]![=sum([Winning Bid Amount])])+IIf(IsError([Reports]![Final Bid Sheets]![Final bid Sheet Party 1 subreport]![=sum(Amount)]),0,[Reports]![Final Bid Sheets]![Final bid Sheet Party 1 subreport]![=sum(Amount)])+IIf(IsError([Reports]![Final Bid Sheets]![Final bid Sheet Party 2 subreport]![=sum(Amount)]),0,[Reports]![Final Bid Sheets]![Final bid Sheet Party 2 subreport]![=sum(Amount)])))) 
 

I'm almost ready to take an adding machine and hand write in the totals at this point. This just doesn't seem like it should be so hard.

CindySue,
where did this syntax come from?

change this:
[quote=CindySue]So I replaced
[Auction Total] with [=sum([Winning Bid Amount])]
[Party1 Total] with [=sum(Amount)]
[Party2 Total] with [=sum(Amount)]

to this:

[Auction Total] with =sum([Winning Bid Amount])
[Party1 Total] with =sum([Amount])
[Party2 Total] with =sum([Amount])

[CindySue]

I was working on my response before I saw your clarification. When you stated "Name is control name. Control source is the property just below the name. The control source is what you should be using in the summations" I took that to mean that I should be using the line below the name which says Control Source in place of the names that I had been using in my calculations, so I substituted what was in Control Source for the name--hence [Auction total] became [=sum([Winning Bid Amount])], etc. I read in your clarification that I should be using the names, so back to [Auction total] the "Name" on the property sheet and my code looks like this:

=Val(Sum(Nz(IIf(IsError([Reports]![Final Bid Sheets]![Final bid Sheet Auction Items subreport]![Auction Total]),0,[Reports]![Final Bid Sheets]![Final bid Sheet Auction Items subreport]![Auction Total])+IIf(IsError([Reports]![Final Bid Sheets]![Final bid Sheet Party 1 subreport]![Party1 total]),0,[Reports]![Final Bid Sheets]![Final bid Sheet Party 1 subreport]![Party1 total])+IIf(IsError([Reports]![Final Bid Sheets]![Final bid Sheet Party 2 subreport]![Party2 total]),0,[Reports]![Final Bid Sheets]![Final bid Sheet Party 2 subreport]![Party2 total]))))

which just gives me #Error.
I think I just figured out how to include a file, if that helps.