Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    I have a report with several fields. Four fields are used in a calculation as a source of a control.

    If the fields are A, B, C and D, and A and B can equal zero and not equal zero, that leaves four possible combinations:
    • IF A <> 0 and B <> 0 then value should be C-D
    • IF A<>0 and B = 0 then value should be 0 - D
    • IF A = 0 and B <> 0 then value should be D
    • IF A = 0 and B = 0 then value should be 0

    I have used a nested IIF in the past, but there were only three possible outcomes. This one is kicking my butt. I started with the source for the control:

    =IIF(A<>0,IIF(B<>0,C-D,0-D),0) and several iterations with no success. I am no longer able to see the forest for the trees.

    Can someone help me?

    Thanks in advance for your ideas.

    Ken

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try

    =IIf(A=0,IIf(B=0,0,D),IIf(B=0,-D,C-D))

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hans,

    As always, you are brilliant. You make is look so simple.

    THANKS!

    Ken

Posting Permissions

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