IIf Statement In Query Problem

benallen002

Board Regular
Joined
Feb 16, 2005
Messages
65
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.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
try using the NZ function instead:

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

hth,
Giacomo
 

benallen002

Board Regular
Joined
Feb 16, 2005
Messages
65
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

Board Regular
Joined
Feb 16, 2005
Messages
65
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.
 

Forum statistics

Threads
1,136,702
Messages
5,677,290
Members
419,684
Latest member
BOB101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top