IIf Statement In Query Problem

[benallen002]

Hi guys, sorry if this seems like a dumb question, but I am still getting used to Access. I am creating a query that is a totals table based on another query. My SQL statement looks like this:

SELECT Sum(qryLabAsgn.Expr1) AS SumOfExpr1, IIf(Sum(qryLabAsgn.Expr2)=0,"No Total",Sum(qryLabAsgn.Expr2)) AS SumOfExpr2, Sum(qryLabAsgn.Expr3) AS SumOfExpr3
FROM qryLabAsgn;

(I want to apply the IIf statement to all three fields, but am holding off until i can figure this out)

It works fine as long as any of the field in the original query have numbers in them (even a 0), but what happens is that if all the rows in a field are empty, it doesn't display the "No Total" string, it acts as if the IIf statement is false and gives me another empty cell. So I guess my question is, what would be the appropraite IIf statement to check if all the rows in a field contain no values at all?

Thanks in advance for any and all help anyone can be on the matter.

 

[giacomo]

try using the NZ function instead:

NZ(Sum(qryLabAsgn.Expr2),"No Total")

hth,
Giacomo

 

[benallen002]

The NZ function worked great if there are no values in any of the rows, but IF any of the rows have numbers, then it multiplies them by a number I can't figure out. Can I use NZ inside of an IIF statement? Are the are any more parameters for NZ that can help with the issue?

I'm looking through the Help files now, but can't find anything that explains why I am getting random values, and not SUMs when there are values in the field and not just all NULLs.

Thanks for the help.

 

[benallen002]

I'm sorry...I'm an idiot.

You're solution worked fine. I couldn't even remember what the output was supposed to be and that is why I thought it wasn't working right. Sorry for the confusion.

Thank you very much for the help. Worked like a charm.