Software

Calculating Access fields with Null values

Don't let Null values derail your formulas. See how to use the NZ function to convert the values to zeroes.

You've created a query that calculates an invoice total by adding the order total and the shipping charges. You used this formula for the calculated field:

Invoice_Total: [Shipping Charge]+[Order Total]

Everything worked fine until about a week ago, when your company began running a special promotion that offered free shipping for orders of $60 and above . As a result, many of Shipping Charge fields are blank, and so are their corresponding Invoice Total fields. The Invoice_Total formula, which includes arithmetic operators, just won't include records containing blank (Null) values in their calculations. Fortunately, the NZ function converts Null values to zeroes so they are included in a calculation. Follow these steps:

  1. Open the query in design view.
  2. Delete the Invoice_Total formula.
  3. Right-click the Invoice_Total field cell and click Zoom.
  4. At the prompt enter the following formula:

Invoice_Total:NZ ([Shipping Charge],0)+NZ([Order Total],0)

  1. Click OK.

Now when you run the query, orders without shipping charges will show an Invoice total:


Miss an Access tip?

Check out the Microsoft Access archive and catch up on other Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

4 comments
Willie11
Willie11

How come the invoice totals that have shipping charges include dollars and cents and the ones that do not have shipping charges only include dollars? This formatting of the result makes the output very confusing.

m.d.barron
m.d.barron

It's amazing how painfully obvious things are when someone slaps the information in front of you. I've inherited a database with numerous calculations involving nulls - the sub-queries were built with equal joins where outers were needed. I knew the NZ function existed, but completely forgot about the "magic" it can do.

pawintx
pawintx

This may help me with an Oracle ODBC error I receive when I reference a query from Excel that has calculated fields that contain null values. The only other work around I've found so far was to export the individual fields and do the calculation in Excel.

Editor's Picks