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
+ 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 =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
14 Replies

 Expert 100+ P: 1,287 Try this: Expand|Select|Wrap|Line Numbers = 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 AllocationCost.Report.txtAllocation I added this to txtAllocationCost Expand|Select|Wrap|Line Numbers =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

 Expert Mod 15k+ P: 31,709 I think you need : Expand|Select|Wrap|Line Numbers =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

 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 =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 =Nz(Sum([NetAmount]),0) On the Main Report footer for AP Expenses: I have this on the control Expand|Select|Wrap|Line Numbers =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 =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 =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

 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 =IIf(APExpenses.Report.HasData,APExpenses.Report.txtAPNet,0) PS. Nicely explained. Made answering very easy. Jun 8 '09 #9

 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 =IIf(PayExpenses.Report.HasData,PayExpenses.Report.txtPayAmount,0) 3. Expand|Select|Wrap|Line Numbers =IIf(AllocationCost.Report.HasData,AllocationCost.Report.txtAllocation,0) Jun 8 '09 #10

 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 =IIf(APExpenses!HasData,APExpenses!txtAPNet,0) 2. Expand|Select|Wrap|Line Numbers =IIf(PayExpenses!HasData,PayExpenses!txtPayAmount,0) 3. Expand|Select|Wrap|Line Numbers =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

 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

 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

 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.