I need to write up a nice demo with examples, but here’s the short version: if you are creating aggregate queries in Microsoft Access 2007 (though it should work in previous versions, too) and end up with funny math because Access won’t subtract a null from a numeric value (or add one to a numeric value, or perform any other bit of math involving nulls), you can force it to treat nulls as zeros by wrapping your statement with the NZ() function.
In my case, I had two aggregate columns: 1) giftReceivedAmount, and 2) giftPledgeAmount (both sum aggregates) that I wanted to sum together in a new column called Total. Ordinarily, you do that in Access by creating a new column in your query and calling it something like this:
Total: [giftReceivedAmount] + [giftReceivedAmount]
The bit to the left of the colon is the (arbitrary) name you want to use for the column. Everything to the right of the colon is the math to compute the result.
That works fine as long as the columns you are adding both have a value. But, if either of them is null, the result will also be null. To force it, you wrap each column reference with the NZ() function. The second argument indicates that you want the nulls treated as zeros. So the statement for the column looks like this:
Total: Nz([SumOfgiftReceivedAmount],0)+Nz([SumOfgiftPledgeAmount],0)
The title of the column is a second “gotcha,” as their titles in the Query Builder Grid don’t include the “SumOf” prefix. But, since they’re aggregated (i.e. the setting on the “Total:” row for each is “Sum”), Access refers to them by this (hidden) name. Leave it off and you get “You tried to execute a query that does not include the specified expression . . . as part of an aggregate function.” If you switch to SQL view, you’ll see that Access uses an AS in the SELECT statement to rename the aggregate columns (e.g. “SELECT Sum(tblGift.giftReceivedAmount) AS SumOfgiftReceivedAmount, . . . “).
Great, a simple explanation that makes sense to non vba people
I love it… but you’ve sadly neglected one of the most mysterious qualities about nz(): Access doesn’t capitalize it as it does with Sum(), Average(), and so on. I’m glad that by CAPITALIZING it you’ve given this little critter the respect he deserves!
I’d like to add: you can us nz() when you want to standardize reports, e.g. always retaining the same number of columns. Suppose you have 100 stores, and you’re listing the sales of a particular item (GIZMO_SALES). Changing the GIZMO_SALES to nz([GIZMO_SALES],0) will make sure every store appears on the report, even if they sold nothing. Otherwise, Access may not include the null columns.
Hi, I have the same problem when one of the two calculated fields has no value the query gives no result. i have tryed the Nz solotion as mentioned above but the error message still occurs.
Query 1)
ELECT PPbudgeturenreg.ProjectId, PPbudgeturenreg.[Aantal uren], PPbudgeturenreg.Uurcode, PPuurcodes.[Uurtarief intern], Sum([Aantal uren]*[Uurtarief intern]) AS Exp
FROM PPuurcodes INNER JOIN PPbudgeturenreg ON PPuurcodes.UurcodeId = PPbudgeturenreg.Uurcode
GROUP BY PPbudgeturenreg.ProjectId, PPbudgeturenreg.[Aantal uren], PPbudgeturenreg.Uurcode, PPuurcodes.[Uurtarief intern];
Query2)
ELECT [PPQuerybudgeturen subtotal].ProjectId, Sum([PPQuerybudgeturen subtotal].Exp) AS SomVanExp
FROM [PPQuerybudgeturen subtotal]
GROUP BY [PPQuerybudgeturen subtotal].ProjectId;
I have filed in the Nz statement as mentioned above but it does’nt work.
does anybody knows why?
Thanks
Jim
THANK YOU!!!! This helped me figure out how to fix my reports!
Thank you – this was the most simple explaination for the function. It worked perfectly.
You saved the day.
Happy to help, Alice!
Great help. Thanks
Is possible to sum up the calculated line totals on a form footer? it keeps giving me an #Error message even after using =Nz(Sum([field],0) any idea?
Regards
Chris
Is it possible to sum up the calculated line totals on a form footer access database 2016? it keeps giving me an #Error message even after using =Nz(Sum([field],0) any idea?
Regards
Chris
Do you mind if I quote a few of your posts as long
as I provide credit and sources back to your webpage? My blog site is in the very same niche
as yours and my visitors would definitely benefit from a
lot of the information you present here. Please
let me know if this alright with you. Many thanks!
I don’t mind at all. Go right ahead.