IIf Statement for Null Value

[MNNovice]

My main report contains 3 separate expense sub reports. For my sub report footers I used (one of the sub report shown here) I have this statement

=IIf(IsNull([Amount]),0,Sum([Amount]))

My goal is to show a zero on the main report footer for those sub reports that don't have any data. This way the footer of the main report should summarirze the grand total for all 3 expenses regardless of there is data or no data. But instead, I get this "#Error" for those sub reports that don't have data.

What will be the correct formula? Thanks.

[ChipR]

Try this:

= Nz(SUM([Amount]),0)

[MNNovice]

ChipR:

Sorry but it's still not working. Let me give a little more details.

A text box called txtAllocationCost is on the sub report

Main Report footer has this to show the total from the sub report:

AllocationCost.Report.txtAllocation

I added this to txtAllocationCost

=Nz(SUM([Amount]),0) 

The main report still showing #Error for Allocation Cost Total

What am I doing wrong? Thanks.

[ChipR]

First, do the sub reports have zero in the footers if there is no data?

[MNNovice]

First, do the sub reports have zero in the footers if there is no data?

No. If there is no data it just shows the labels without any data.

[NeoPa]

I think you need :

=Sum(Nz([Amount],0))

This all depends on [Amount] being a correct reference though.

If not, then you need to provide names of the relevant items. The subreport control would be required as well as how the item is referenced within the report itself (the report used within the subreport control).

Repeat a further two times to handle all three figures or take the concept and progress yourself from there, whichever is better for you.

[Denburt]

When I want to check if the subreport has data then I usually use the following:

=IIf (SomeSubReport.Report.HasData,Sum(Nz([Amount],0)),0)

[MNNovice]

Denburt:

It's working for one control and not for the other two. I must have some name(s) messed up.

1. Ap Expenses
Sub Report control on the main report is called APExpenses
The control containing the total amount on the sub report is called txtApNet and the actual field is called NetAmount

So on the sub form I have this in txtApNet:

=Nz(Sum([NetAmount]),0)

On the Main Report footer for AP Expenses: I have this on the control

=IIf(APExpenses.Report.HasData,Sum(Nz([NetAmount],0)),0)

I got this: #Error

2. Payroll Expenses
Sub Report control on the main report is called PayExpenses
The control containing the total pay amount on the sub report is called txtPayAmount and the actual field is called PayAmount

So on the sub form I have this in txtPayAmount:
[code]=Nz(Sum([PayAmount],0))CODE]

On the Main Report footer for Payroll Expenses: I have this on the control

=IIf(PayExpenses.Report.HasData,Sum(Nz([PayAmount],0)),0)

I got this: #Error

3. Allocation Cost
Sub Report control on the main report is called AllocationCost
The control containing the total pay amount on the sub report is called txtAllocation and the actual field is called Amount

So on the sub form I have this in txtAllocation:
[code]=Nz(Sum([Amount],0))CODE]

On the Main Report footer for Payroll Expenses: I have this on the control

=IIf(AllocationCost.Report.HasData,Sum(Nz([Amount],0)),0)

This one gives correct result. What's wrong with the other two?

I know you are going to say, check those names...(LOL). I did.

Thought I share this with you.

M

[NeoPa]

I'll try to answer one at a time (any that I can at least).

1. You need :

=IIf(APExpenses.Report.HasData,APExpenses.Report.txtAPNet,0)

PS. Nicely explained. Made answering very easy.

[NeoPa]

2 & 3.

Now I'm starting to get confused. I would never expect that code to work correctly, as I see no reference in [Amount] to the sub report. Maybe I'm missing something fundamental, but let me put in suggestions here for the two others and you can see if they work and decide where to go from there.

2.

=IIf(PayExpenses.Report.HasData,PayExpenses.Report.txtPayAmount,0)

3.

=IIf(AllocationCost.Report.HasData,AllocationCost.Report.txtAllocation,0)

[NeoPa]

One other point to mention, which is worth a try but I haven't tested it, is that YourSubreport.Report.Control can be rewritten as YourSubreport!Control. You could try this instead if you find the others work.
1.

=IIf(APExpenses!HasData,APExpenses!txtAPNet,0)

2.

=IIf(PayExpenses!HasData,PayExpenses!txtPayAmount,0)

3.

=IIf(AllocationCost!HasData,AllocationCost!txtAllocation,0)

[MNNovice]

NeoPa:

I used formula in posting 9 and 10 and it's working fine. Thank you so much. You made my MONDAY.

Although I copied the alternet formula in my notebook (from Bytes), I don't think I am going to attempt it now. May be when I have some leisure time.

Thanks my friend.

M

[NeoPa]

I'm very pleased to hear it M.

I think Denburt was trying principally to indicate the reference you need to use to determine whether the subreport had anything to show or not. I don't believe he was suggesting the syntax to reference the total as such. I'm only confused that even one of them worked earlier.

That doesn't really matter though. It works and that's great.

[Denburt]

Glad it is working M thanks for the clarification Neo, obviously I was in a rush to help answer to the question and didn't look it over as thoroughly as I should have. I have been seriously slammed over here and it doesn't look like it's letting up anytime soon.

Have a good day.

[NeoPa]

No worries mate. That's perfectly understandable, and your post certainly helped me to get the question into perspective properly. It was very helpful.