# Thread: Sum 2 IIF Txt Boxes (2003 SP1)

1. ## Sum 2 IIF Txt Boxes (2003 SP1)

I have a Report that calculates the Total amount due based on certain criteria, ie,

=IIf([FeeBand]='U',([Sum Of budget_amount]/2),"")
=IIf([FeeBand]='T',([Sum Of budget_amount]*10/100),"")

These 2 Controls are called txtUnsecuredAchieve & txtTargetAchieve

I tried to create another Text Box that would sum these Totals =Sum([txtTargetAchieve])+([txtUnsecuredAchieve]), but I just get an error message, and I don't understand what I'm doing wrong. Is it not possible to Sum two IIF Fields?

2. ## Re: Sum 2 IIF Txt Boxes (2003 SP1)

I would use :
=IIf([FeeBand]='U',([Sum Of budget_amount]/2),0)
=IIf([FeeBand]='T',([Sum Of budget_amount]*10/100),0)

and to total those two, use =[txtTargetAchieve]+[txtUnsecuredAchieve]
as the sum (I suppose) is already done in the textboxes.

Or do I missunderstand and you want.
=Sum([txtTargetAchieve])+Sum([txtUnsecuredAchieve]),

3. ## Re: Sum 2 IIF Txt Boxes (2003 SP1)

You're correct in that putting a 0 instead of "" gets the calculation to work (kind of), but there are 2 problems as I have the 2 text boxes overlayed to ensure that I only get a result if the condition is true (ie, I don't want to see

4. ## Re: Sum 2 IIF Txt Boxes (2003 SP1)

Try

=IIf([FeeBand]='U',([Sum Of budget_amount]/2),Null)
=IIf([FeeBand]='T',([Sum Of budget_amount]*10/100),Null)

=Sum(IIf([FeeBand]='U',([Sum Of budget_amount]/2),Null))+Sum(IIf([FeeBand]='T',([Sum Of budget_amount]*10/100),Null))

5. ## Re: Sum 2 IIF Txt Boxes (2003 SP1)

Thank you both for your replies.

The Null Element works, meaning I can overlay the text boxes and only get one displaying (as should be), but with the second argument supplied, I get prompted to enter the Value of Sum Of budget_amount, meaning that I get a blank result?

<img src=/S/confused.gif border=0 alt=confused width=15 height=20>

Thanks again for your help. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

6. ## Re: Sum 2 IIF Txt Boxes (2003 SP1)

What is "Sum Of budget_amount"? Is it the name of a field in the record source of the report, or is it the name of a control on the report?

7. ## Re: Sum 2 IIF Txt Boxes (2003 SP1)

It's the Name of the Text Box on the Fee Band Footer---its Control Source is =Sum([budget_amount]).

Does that help locate the problem?

8. ## Re: Sum 2 IIF Txt Boxes (2003 SP1)

Yep, it does. You cannot sum a control, only a field in the record source (or an expression based on a field in the record source). One possible solution goes as follows:

Duplicate the text boxes txtUnsecuredAchieve & txtTargetAchieve within the Fee Band footer.
Set the Visible property of the duplicates to No.
Set the Running Sum property of the duplicates to Over All.
Name them for example txtUnsecuredRunning and txtTargetRunning.
Put a text box in the report footer with control source
=[txtUnsecuredRunning]+[txtTargetRunning]

9. ## Re: Sum 2 IIF Txt Boxes (2003 SP1)

Hans

You are SUCH a genius--I wish I had your brains and knowledge.

Thanks so much.

All I had to change was 'Over All' to 'Over Group', as that's actually what I wanted, and it worked.

You're a star!

Thanks again (to both of you) for your replies.

God Bless

<img src=/S/starstruck.gif border=0 alt=starstruck width=15 height=15> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

10. ## Re: Sum 2 IIF Txt Boxes (2003 SP1)

OK, this is embarrassing, but I can't get a Grand Total at the End of the Report. I've tried all the Running Sum Options, and I tried to duplicate the solution outlined above, but that didn't work either.

Sorry for being so stupid, but . . . Help?

11. ## Re: Sum 2 IIF Txt Boxes (2003 SP1)

Grand total of what? Please provide specific information.

12. ## Re: Sum 2 IIF Txt Boxes (2003 SP1)

Sorry, the Grand Total of the Running Totals, ie, =[txtTargetRunning]+[txtUnsecuredRunning] (called txtTotalACTUAL).

Thanks. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

13. ## Re: Sum 2 IIF Txt Boxes (2003 SP1)

If you put a text box with control source =[txtTargetRunning]+[txtUnsecuredRunning] in the report footer, it should display the grand total. (I thought you had already done that)

14. ## Re: Sum 2 IIF Txt Boxes (2003 SP1)

No, believe it or not, that just gives me the same figure as that which appears as the final Amount on the Last Detail Page of the Report, eg, If the Final Unsecured Amount is

15. ## Re: Sum 2 IIF Txt Boxes (2003 SP1)

Have you tried yet another set of duplicates with Running Sum set to Over All, and a text box in the report footer that refers to these?

If that doesn't do what you want, I don't understand the situation.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•