Sum(IIF with a Null condition in a query

[Moxioron]

Hello all.

I am having an issue with an expression in a query.

What I am trying to do is to sum [Account Balance Total Amount] IF the [External Status Code] is null.

When I use Expr1: Sum(IIf([External Status Code]=" ",[Account Balance Total Amount],0)) I get a zero result

When I use Expr1: Sum(IIf([External Status Code]IsNull,[Account Balance Total Amount],0)) I get Invalid Syntax

When I use Expr1: Sum(IIf([External Status Code],IsNull,[Account Balance Total Amount],0)) I get Wrong Number of Arguments

Thanks for your help.
​

 

[pbaldy]

Try

Sum(IIf(IsNull([External Status Code]), [Account Balance Total Amount], 0))

 

[boblarson]

Just an FYI, you can use Is Null (in fact it is better than using the function IsNull as it is native SQL - Pat Hartman has posted about that, but just don't remember where it is). But in the example given, you need a space after the square bracket and after the Is portion:

Expr1:Sum(IIF([External Status Code] Is Null, [Account Balance Total Amount], 0))

 

[Moxioron]

Thanks pbaldy! Worked.

Thanks for the information Bob.

 

[pbaldy]

Happy to help!