Chained IF THEN statement

[nick22]

Hi everyone,

I am in need of some help in writing a chained IF THEN.

I have four fields. I need the IF THEN to look at field one, then if field one is not null take that number otherwise move to field two. and so one down the line for the four fields.

Here is what I have for a formula now:

IF IsNull([Quote - Grand Total (converted)])
THEN [Quote - Grand Total (converted)]
ELSEIF IsNull([Quote - Grand Total w/ PEV (converted)])
THEN [Quote - Grand Total w/ PEV (converted)]
ELSEIF IsNull([Grand Total (converted)])
THEN [Grand Total (converted)]
ELSEIF IsNull([Total Product and PEV (converted)])
THEN [Total Product and PEV (converted)]
ELSE 0
ENDIF

currently this returns a 0.

Any suggestions?

[NicoleJohnson · Accepted solution]

If you are trying to return the value only if the field is not Null, then I think you just need to add an ! in front of each IsNull statement (which makes it "If is not null" rather than "If is null").

IF !IsNull([Quote - Grand Total (converted)])
THEN [Quote - Grand Total (converted)]
ELSEIF !IsNull([Quote - Grand Total w/ PEV (converted)])
THEN [Quote - Grand Total w/ PEV (converted)]
ELSEIF !IsNull([Grand Total (converted)])
THEN [Grand Total (converted)]
ELSEIF !IsNull([Total Product and PEV (converted)])
THEN [Total Product and PEV (converted)]
ELSE 0
ENDIF

Let me know if that does the trick! :)

NJ

[nick22]

Thank you for your help, This formula worked. I figured it out a different way heres what I used.

//Quote - GrandTotal
IF [Quote - Grand Total (converted)] > 0 Then
   [Quote - Grand Total (converted)]
//[Quote - Grand Total w/ PEV]
ElseIf [Quote - Grand Total w/ PEV (converted)] > 0 Then
       [Quote - Grand Total w/ PEV (converted)]
//[Grand Total]
ElseIf [Grand Total (converted)] > 0 Then
    [Grand Total (converted)]
//[Total Product and PEV]
ElseIf [Total Product and PEV (converted)] > 0 Then
       [Total Product and PEV (converted)]
else 0.00
EndIf

[MarqueeCrew]

@nick22 & @NicoleJohnson,

It may be possible to use:

Max(
[Quote - Grand Total (converted)],
[Quote - Grand Total w/ PEV (converted)],
[Grand Total (converted)],
[Total Product and PEV (converted)],
0
)

Just a thought....

Mark