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