Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    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?

    Thanks in advance for your replies.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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]),
    Francois

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    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. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #9
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    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. #10
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    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. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sum 2 IIF Txt Boxes (2003 SP1)

    Grand total of what? Please provide specific information.

  12. #12
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    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. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #14
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    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. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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 LastLast

Posting Permissions

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