Sum(IIF with a null criteria.....

[Moxioron]

Hell 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.

 

[Joe4]

I would recommend starting off with a simple Select Query.
Add the two fields you have listed. Place "Is Null" in the Criteria row of the "External Status Code" field.
Then, change to an Aggregate Query by clicking on the Totals button (looks like a Sigma).
This will add a "Total Row" to the QBE. Under the "Account Balance Total Amount" field, change the value in that row from "Group By" to "Sum".
That should give you your Total.

 

[Fazza]

SQL could be

SELECT SUM(name of field to sum) AS [final name of summed field], comma separated list of other fields to return
FROM your_data_source
WHERE [External Status Code] Is Null
GROUP BY comma separated list of other fields to return

 

[Moxioron]

Thanks Joe 4. I was just trying to avoid multiple queries when the database is already so large.

I got the answer from another forum. Sum(IIf(IsNull([External Status Code]), [Account Balance Total Amount], 0)) <!-- / message --><!-- Adsense inside first post-->

<!-- / Adsense inside first post--><!-- Adsense inside third post--><!-- / Adsense inside third post--><!-- Adsense inside last post--><!-- / Adsense inside last post--><!-- sig -->

 

[Joe4]

I got the answer from another forum

I am not sure if you mean that you found the answer on another forum, or if you posted the question on another forum and got an answer.
If the latter, we ask that you need rules #10 here: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html