Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Double if? (2000)

    Here is the statement that works:

    2003GA: Sum(IIf(Format([DATE],"yyyy")="2003",[Gross Adds]))

    Problem is... this will return a zero value for many date that are being queried and I am charting these results. I hate when the line graph drops to the bottom (zero) line for a part of the month that has not yet happened.

    How do I alter the above to add "if [gross adds] = 0 then give me a null result"
    There is always a way.

  2. #2
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Double if? (2000)

    As confusing as my message may have been you read me right!

    I used your suggestion and it did exactly what I was looking for! THANKS VERY MUCH!
    There is always a way.

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

    Re: Double if? (2000)

    If I understand your intention correctly, you don't want the result to be Null if [Gross Adds] is zero, but if Sum(...) is zero. Try

    2003GA: IIf(Sum(IIf(Format([DATE],"yyyy")="2003",[Gross Adds]))=0,Null,Sum(IIf(Format([DATE],"yyyy")="2003",[Gross Adds])))

    If you would subsitute Null for 0 in [Gross Adds] itself, that wouldn't help, since Nulls in Sum(...) are ignored. Post back if I misread your intention.

Posting Permissions

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