The mysterious NZ() function in Microsoft Access

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, . . . “).

Posted in Microsoft Access. Bookmark the permalink. RSS feed for this post. Leave a trackback.

7 Responses to The mysterious NZ() function in Microsoft Access

  1. Aileen says:

    Great, a simple explanation that makes sense to non vba people

  2. Michael Hesser says:

    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.

  3. Jim says:

    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

  4. Jenni McClure says:

    THANK YOU!!!! This helped me figure out how to fix my reports!

  5. Pingback: Fun with Google Analytics | wheatblog

  6. Alice Colyar says:

    Thank you – this was the most simple explaination for the function. It worked perfectly.
    You saved the day.

  7. wheat says:

    Happy to help, Alice!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Swedish Greys - a WordPress theme from Nordic Themepark.