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:
- Open the query in design view.
- Delete the Invoice_Total formula.
- Right-click the Invoice_Total field cell and click Zoom.
- At the prompt enter the following formula:
Invoice_Total:NZ ([Shipping Charge],0)+NZ([Order Total],0)
- 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.
