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