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]

You have to give Nz a value to return if Null. Nz(FieldName, 0)

[puppydogbuddy]

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.

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:

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

[CindySue]

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:

=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:

 =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

=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?

[puppydogbuddy]

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 (.............. )))

[jaxjagfan]

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.

[CindySue]

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.j pg[/IMG]

Thanks again for trying to help.

[puppydogbuddy]

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.j pg[/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

[CindySue]

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?

[puppydogbuddy]

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(N z([Reports]![Final Bid Sheets]![Final Bid Sheet Party 1 subreport]![Party1 total],0)))+Val(Sum(N z([Reports]![Final Bid Sheets]![Final Bid Sheet Party 2 subreport]![Party2 total],0)))

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