By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,584 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Calculating values from subreports when one or more is null

P: 52
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:

Expand|Select|Wrap|Line Numbers
  1. =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.
Jan 8 '08 #1
Share this Question
Share on Google+
28 Replies


Rabbit
Expert Mod 10K+
P: 12,316
You have to give Nz a value to return if Null. Nz(FieldName, 0)
Jan 8 '08 #2

puppydogbuddy
Expert 100+
P: 1,923
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:

Expand|Select|Wrap|Line Numbers
  1. =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.
If your subtotals are in a header or footer (as opposed to a detail line), I think you have to use the keyword "Sum" for each subtotal as follows:

Expand|Select|Wrap|Line Numbers
  1. =Sum(Nz([Reports]![Final Bid Sheets]![Final Bid Sheet Auction Items subreport]![Auction Total]))+Sum(Nz([Reports]![Final Bid Sheets]![Final Bid Sheet Party 1 subreport]![Party1 total]))+Sum(Nz([Reports]![Final Bid Sheets]![Final Bid Sheet Party 2 subreport]![Party2 total])) 
Jan 8 '08 #3

P: 52
Thanks for the suggestions, but I'm still not getting anything to work. I must still be missing something, or didn't understand exactly what I was supposed to do.

I tried three different things--
This, which includes the zero value:
Expand|Select|Wrap|Line Numbers
  1. =Nz([Reports]![Final Bid Sheets]![Final Bid Sheet Auction Items subreport]![Auction Total],0)+Nz([Reports]![Final Bid Sheets]![Final Bid Sheet Party 1 subreport]![Party1 total],0)+Nz([Reports]![Final Bid Sheets]![Final Bid Sheet Party 2 subreport]![Party2 total],0) 
Which gives me a total when all three are present, but still an error if one or more is null.

I also tried this, which adds the sum function but uses no zero values:
Expand|Select|Wrap|Line Numbers
  1.  =Sum(Nz([Reports]![Final Bid Sheets]![Final Bid Sheet Auction Items subreport]![Auction Total]))+Sum(Nz([Reports]![Final Bid Sheets]![Final Bid Sheet Party 1 subreport]![Party1 total]))+Sum(Nz([Reports]![Final Bid Sheets]![Final Bid Sheet Party 2 subreport]![Party2 total]))
which gives me zeros no matter what.

And this, which adds the zero value and uses sum
Expand|Select|Wrap|Line Numbers
  1. =Sum(Nz([Reports]![Final Bid Sheets]![Final Bid Sheet Auction Items subreport]![Auction Total],0))+Sum(Nz([Reports]![Final Bid Sheets]![Final Bid Sheet Party 1 subreport]![Party1 total],0))+Sum(Nz([Reports]![Final Bid Sheets]![Final Bid Sheet Party 2 subreport]![Party2 total],0)) 
but this gives me zeros no matter what as well.

Does anyone see what I'm still doing wrong?
Jan 8 '08 #4

puppydogbuddy
Expert 100+
P: 1,923
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.
In light of your statements above, there is something that you have not explained. the nz function, will not make numbers disappear if they were there, it will only substitute 0 when there is a null present. Which brings me to another thought ....Is it possible the columns are not null, but have spaces....in which case, you need to wrap each subtotal in the Val function.....so that it looks like this >>>>>Sum(Val(Nz(..............)))
Jan 8 '08 #5

jaxjagfan
Expert 100+
P: 254
Instead of subreports have you tried using "Groupings" in your reports. It looks like you are reporting on "Bids". Make an aggegate query with the maximum number of columns you want to see - include the group by's, sum's, count's etc.

Then make a report based on this query.

This will allow you to include details in the groups necessary and will allow summaries in report, page, and group headers and footers. If one bid has 2 groups and the next has 5 it will not matter.

Try to handle exceptions before the report - it is normally easier to handle and easier to troubleshoot most of the time.
Jan 8 '08 #6

P: 52
Perhaps null is not the appropriate word. Maybe this will make it clearer. There are three tables--One called auction items, one called Party 1 and one called Party 2, which is identical in structure to Party 1. In all three tables is a field called winning bidder number and winning bid amount, among others. A single wining bidder number may appear multiple times in one, two or all three of the tables.

I have a report called Final Bid Sheets, based on a query of all winning bidder numbers that appear in any one or more of the three tables, and the only field on it is the winning bidder number, along with some text. I have a subreport for Auction Items, Party 1 and Party 2 that lists the winning bid amount field and another field or two and links to the main form by the winning bidder number. The first subreport shows all auction items matching the winning bidder number on the main form, grouped by Live or Silent and their amounts, then a total for those amounts. Party 1 shows the attendee number field and the amounts in the winning bid amount and then has a total, and Party 2 is identical to Party 1. I want to add the totals from each subreport, if there is one, so that there is a grand total on the main report, but if there are no entries for one or more of the subreports, I can't get the formula to work.

I've tried to attach a shot of what I'm trying to get, but I've never done that before, and I'm not sure I did it right. [IMG]c:\screenshot.jpg[/IMG]

Thanks again for trying to help.
Jan 8 '08 #7

puppydogbuddy
Expert 100+
P: 1,923
Perhaps null is not the appropriate word. Maybe this will make it clearer. There are three tables--One called auction items, one called Party 1 and one called Party 2, which is identical in structure to Party 1. In all three tables is a field called winning bidder number and winning bid amount, among others. A single wining bidder number may appear multiple times in one, two or all three of the tables.

I have a report called Final Bid Sheets, based on a query of all winning bidder numbers that appear in any one or more of the three tables, and the only field on it is the winning bidder number, along with some text. I have a subreport for Auction Items, Party 1 and Party 2 that lists the winning bid amount field and another field or two and links to the main form by the winning bidder number. The first subreport shows all auction items matching the winning bidder number on the main form, grouped by Live or Silent and their amounts, then a total for those amounts. Party 1 shows the attendee number field and the amounts in the winning bid amount and then has a total, and Party 2 is identical to Party 1. I want to add the totals from each subreport, if there is one, so that there is a grand total on the main report, but if there are no entries for one or more of the subreports, I can't get the formula to work.

I've tried to attach a shot of what I'm trying to get, but I've never done that before, and I'm not sure I did it right. [IMG]c:\screenshot.jpg[/IMG]

Thanks again for trying to help.

CindySue,

Your attachment was not accessible, but maybe this link will help:

http://support.microsoft.com/kb/208835
Jan 9 '08 #8

P: 52
As best I could understand, the Microsoft article actually only dealt with one subreport, and I have three. It provided a way to get a grand total from all the subtotals on that one subreport at the very end of the report, but not a way to include different subreports. It still, however, displayed an error when there were no entries in that particular subreport on that bidder number.

I thought about trying the query method suggested by jaxjagfan, but I'm not sure I understand how to do that and get all records. By aggreate, do I need to use union? And it won't matter that the fields aren't the same between the auction items and party tables?
Jan 9 '08 #9

puppydogbuddy
Expert 100+
P: 1,923
Try this. Chnged the order of the Val function. In Access, the order in which the function executes is important.....

=Val(Sum(Nz([Reports]![Final Bid Sheets]![Final Bid Sheet Auction Items subreport]![Auction Total],0)))+Val(Sum(Nz([Reports]![Final Bid Sheets]![Final Bid Sheet Party 1 subreport]![Party1 total],0)))+Val(Sum(Nz([Reports]![Final Bid Sheets]![Final Bid Sheet Party 2 subreport]![Party2 total],0)))
Jan 9 '08 #10

Rabbit
Expert Mod 10K+
P: 12,316
I misunderstood the original circumstances, use iif() in conjunction with IsError()

Expand|Select|Wrap|Line Numbers
  1. =iif(IsError(Reports!MainReport!SubReport!Control), 0, Reports!MainReport!SubReport!Control)
  2.  
You may or may not need to use Nz, Sum, Val in conjunction with this as well.
Jan 9 '08 #11

P: 52
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:
Expand|Select|Wrap|Line Numbers
  1. =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?
Jan 9 '08 #12

Rabbit
Expert Mod 10K+
P: 12,316
Calculate the sum in a control on the subforms and then call that control from the main form.
Jan 9 '08 #13

puppydogbuddy
Expert 100+
P: 1,923
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.
Jan 9 '08 #14

P: 52
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.
Jan 9 '08 #15

puppydogbuddy
Expert 100+
P: 1,923
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
Jan 9 '08 #16

puppydogbuddy
Expert 100+
P: 1,923
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
Jan 9 '08 #17

P: 52
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:
Expand|Select|Wrap|Line Numbers
  1.  
  2. =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)])))) 
  3.  
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.
Jan 9 '08 #18

puppydogbuddy
Expert 100+
P: 1,923
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:
Expand|Select|Wrap|Line Numbers
  1.  
  2. =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)])))) 
  3.  
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])
Jan 9 '08 #19

P: 52
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:

Expand|Select|Wrap|Line Numbers
  1. =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.
Attached Images
File Type: jpg screenshot.jpg (35.3 KB, 232 views)
Jan 9 '08 #20

puppydogbuddy
Expert 100+
P: 1,923
[quote=puppydogbuddy]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,
Jan 9 '08 #21

puppydogbuddy
Expert 100+
P: 1,923
CindySue,
You have some syntax errors. I will get back to you late tonight, early tomorrow with corrected syntax.
Jan 9 '08 #22

P: 52
I used your code, and added at the end what I thought I needed to to get it to work and I have:
Expand|Select|Wrap|Line Numbers
  1. =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.
Jan 9 '08 #23

P: 52
Thanks. I really hate that I'm being so much trouble. I really do appreciate the help.
Jan 9 '08 #24

puppydogbuddy
Expert 100+
P: 1,923
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.

Expand|Select|Wrap|Line Numbers
  1. 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)
  2.  
Jan 9 '08 #25

P: 52
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?
Jan 9 '08 #26

puppydogbuddy
Expert 100+
P: 1,923
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.
Jan 10 '08 #27

D Giles
P: 11
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.
Feb 14 '08 #28

puppydogbuddy
Expert 100+
P: 1,923
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.
Feb 14 '08 #29

Post your reply

Sign in to post your reply or Sign up for a free account.