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

IIf Statement for Null Value

100+
P: 418
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

Expand|Select|Wrap|Line Numbers
  1. =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.
Jun 4 '09 #1
Share this Question
Share on Google+
14 Replies


Expert 100+
P: 1,287
Try this:
Expand|Select|Wrap|Line Numbers
  1. = Nz(SUM([Amount]),0)
Jun 4 '09 #2

100+
P: 418
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:

Expand|Select|Wrap|Line Numbers
  1. AllocationCost.Report.txtAllocation
I added this to txtAllocationCost
Expand|Select|Wrap|Line Numbers
  1. =Nz(SUM([Amount]),0) 
The main report still showing #Error for Allocation Cost Total

What am I doing wrong? Thanks.
Jun 4 '09 #3

Expert 100+
P: 1,287
First, do the sub reports have zero in the footers if there is no data?
Jun 4 '09 #4

100+
P: 418
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.
Jun 4 '09 #5

NeoPa
Expert Mod 15k+
P: 31,709
I think you need :
Expand|Select|Wrap|Line Numbers
  1. =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.
Jun 4 '09 #6

Denburt
Expert 100+
P: 1,356
When I want to check if the subreport has data then I usually use the following:

Expand|Select|Wrap|Line Numbers
  1. =IIf (SomeSubReport.Report.HasData,Sum(Nz([Amount],0)),0)
Jun 5 '09 #7

100+
P: 418
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:
Expand|Select|Wrap|Line Numbers
  1. =Nz(Sum([NetAmount]),0)
On the Main Report footer for AP Expenses: I have this on the control
Expand|Select|Wrap|Line Numbers
  1. =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
Expand|Select|Wrap|Line Numbers
  1. =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
Expand|Select|Wrap|Line Numbers
  1. =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
Jun 8 '09 #8

NeoPa
Expert Mod 15k+
P: 31,709
I'll try to answer one at a time (any that I can at least).

1. You need :
Expand|Select|Wrap|Line Numbers
  1. =IIf(APExpenses.Report.HasData,APExpenses.Report.txtAPNet,0)
PS. Nicely explained. Made answering very easy.
Jun 8 '09 #9

NeoPa
Expert Mod 15k+
P: 31,709
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.
Expand|Select|Wrap|Line Numbers
  1. =IIf(PayExpenses.Report.HasData,PayExpenses.Report.txtPayAmount,0)
3.
Expand|Select|Wrap|Line Numbers
  1. =IIf(AllocationCost.Report.HasData,AllocationCost.Report.txtAllocation,0)
Jun 8 '09 #10

NeoPa
Expert Mod 15k+
P: 31,709
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.
Expand|Select|Wrap|Line Numbers
  1. =IIf(APExpenses!HasData,APExpenses!txtAPNet,0)
2.
Expand|Select|Wrap|Line Numbers
  1. =IIf(PayExpenses!HasData,PayExpenses!txtPayAmount,0)
3.
Expand|Select|Wrap|Line Numbers
  1. =IIf(AllocationCost!HasData,AllocationCost!txtAllocation,0)
Jun 8 '09 #11

100+
P: 418
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
Jun 8 '09 #12

NeoPa
Expert Mod 15k+
P: 31,709
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.
Jun 8 '09 #13

Denburt
Expert 100+
P: 1,356
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.
Jun 9 '09 #14

NeoPa
Expert Mod 15k+
P: 31,709
No worries mate. That's perfectly understandable, and your post certainly helped me to get the question into perspective properly. It was very helpful.
Jun 9 '09 #15

Post your reply

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