How to add total of two different subreport when one or both might be null?

[jaad]

Hello,

I have a main report which contains two subreports. Each subreport calculates a total of a group of items.

I would like to show the grand total in the main report at the bottom

my fields are:

AccessInvTotal
AccessLabourTot al

I;ve tried the following line in the record source of my grand total field and I can' t get it to work given that sometime one of the two subreport has no value to return:

= [BackChargeProjectTimeSR].Report![AccessLabourTotal] + [BackchargeInvTenantSR].Report![AccessInvTotal] 

IIF(Isnull([BackChargeProjectTimeSR].Report![AccessLabourTotal],0) + IFF(Isnull([BackchargeInvTenantSR].Report![AccessInvTotal],0)

NZ([BackChargeProjectTimeSR].Report![AccessLabourTotal]) + NZ([BackchargeInvTenantSR].Report![AccessInvTotal])

[Rabbit]

For Nz, you are missing the parameter where you tell it what to return when there is a null. For the IFf, you are missing the parameter where you tell it what to return when the expression is false.

[jaad]

I've tried that already and it didn't work for me

IIF(Isnull([BackChargeProjectTimeSR].Report![AccessLabourTotal],0,[BackChargeProjectTimeSR].Report![AccessLabourTotal]) + IFF(Isnull([BackchargeInvTenantSR].Report![AccessInvTotal],0,[BackchargeInvTenantSR].Report![AccessInvTotal])

[Stewart Ross]

In all the examples you have shown you are not closing the call to IsNull, and I'm surprised that you are not simply showing #Error in your grand total!

The version posted in post #3 with the missing bracket restored to its correct position in each call should be:

IIF(Isnull([BackChargeProjectTimeSR].Report![AccessLabourTotal]),0,[BackChargeProjectTimeSR].Report![AccessLabourTotal]) + IFF(Isnull([BackchargeInvTenantSR].Report![AccessInvTotal]),0,[BackchargeInvTenantSR].Report![AccessInvTotal])

Nz is simpler to use, at least it is when you specify what you want it to return as Rabbit pointed out.

Nz([BackChargeProjectTimeSR].Report![AccessLabourTotal],0) + Nz([BackchargeInvTenantSR].Report![AccessInvTotal],0)

-Stewart

[jaad]

I've just tried the code line that you wrote and corrected the second "IFF" to "IIF" and it works in report preview like mine did but when I go to print preview I get an [Error]?

[Stewart Ross]

I suspect that the problem is that when a report has no data it is not that the control values are null - they don't exist at all. In which case what you need is to test the HasData property of the report, rather than testing for the control being null:

IIF([BackChargeProjectTimeSR].HasData,[BackChargeProjectTimeSR].Report![AccessLabourTotal], 0) + IIF([BackchargeInvTenantSR].HasData,[BackchargeInvTenantSR].Report![AccessInvTotal], 0)

-Stewart

[jaad]

ok, I've tried it and now I am getting a [#NAME?] error.

[Stewart Ross]

Sorry, forgot they were subreports and not reports in their own right. Interpose the .Report qualifier between the subreport name and the HasData property, like this:

IIF([BackChargeProjectTimeSR].Report.HasData,[BackChargeProjectTimeSR].Report![AccessLabourTotal], 0) + IIF([BackchargeInvTenantSR].Report.HasData,[BackchargeInvTenantSR].Report![AccessInvTotal], 0)

-Stewart

[jaad]

YEEEE... it works perfectly. Thanks a million Stewart. you da man.....

cheers
jaad