Calculating values from subreports when one or more is null

[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.

[puppydogbuddy]

[QUOTE=puppydogb uddy]CindySue,
where did this syntax come from?

change this:

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])

In your code, where you refering to a control that already has the calculation,
you need to refer to it by name as shown.

=Val(Sum(Nz(IIf (IsError([Reports]![Final Bid Sheets]![Final bid Sheet Auction Items subreport]![Auction Total]),0,

[puppydogbuddy]

CindySue,
You have some syntax errors. I will get back to you late tonight, early tomorrow with corrected syntax.

[CindySue]

I used your code, and added at the end what I thought I needed to to get it to work and I have:

=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]))))

and every total is zero.

[CindySue]

Thanks. I really hate that I'm being so much trouble. I really do appreciate the help.

[puppydogbuddy]

CindySue,
Your old code reflects the confusion over when you refer to the name property and when you refer to the control source property. The corrected code below also reflects the syntax recommended by Allen Browne in the link I gave you. I could not test the code, but this code should be very close to the final code. Let me know how it works for you. Thanks.

IIf([Final bid Sheet Auction Items subreport].[Report].[HasData], nz([Final bid Sheet Auction Items subreport]![Auction Total],0),0) + IIf([Final bid Sheet Party 1 subreport].[Report].[HasData], nz([Final bid Sheet Party 1 subreport]![Party1 total],0),0) + IIf([Final bid Sheet Party 2 subreport].[Report].[HasData], nz([Final bid Sheet Party 2 subreport]![Party2 total],0),0)

[CindySue]

That so works!!! Thank you! How do you attach hugs and kisses???

But just to help, as I'm sure someday I'll need something else, what should I have said in the first place to have avoided so much confusion?

[puppydogbuddy]

That so works!!! Thank you! How do you attach hugs and kisses???

But just to help, as I'm sure someday I'll need something else, what should I have said in the first place to have avoided so much confusion?

LOL! I am glad your problem is finally resolved. I think that the confusion over the name vs control source is something that could happen to any of us. Hindsight is 20/20, but I think if we had realized earlier that there were no problems with any of the calculated subreport totals....the problem was just determining the syntax to use to pass the subreport totals to the master total, it might have been resolved sooner.

[D Giles]

Not sure of the protocol on this forum - may I post a reply to this thread a month later, or must I start a new one? Its just that I've been reading this thread over and over again, as I'm having the same problem with referencing a subreport total in the main report footer. The total in the main report only picks up one record amount, but the total in the subreport which it is referenced to reflects the actual total of all the records. Why is the total in the main report footer doing this? I eventually solved the referencing syntax problem and got away from the Name? Error? errors.

[puppydogbuddy]

Not sure of the protocol on this forum - may I post a reply to this thread a month later, or must I start a new one? Its just that I've been reading this thread over and over again, as I'm having the same problem with referencing a subreport total in the main report footer. The total in the main report only picks up one record amount, but the total in the subreport which it is referenced to reflects the actual total of all the records. Why is the total in the main report footer doing this? I eventually solved the referencing syntax problem and got away from the Name? Error? errors.

D Giles,
Site rules require that you start your own post. Be sure and include a reference to this post. Also be sure and post the entire espression you have for the total in your main report.