totn Access

MS Access 2003: Handling Divide by Zero errors in queries

This MSAccess tutorial explains how to handle divide by zero errors in queries in Access 2003 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2003/XP/2000/97, I'm trying to write a formula in a query as follows:

[Price]/[Quantity]

Most of the times this formula works, but in some cases the [Quantity] field is zero so when the formula divides zero by zero, the result comes up as #Error. Is there a way I can tell Access, if dividing by zero, the result is zero?

Answer: You can use the iif function in your Access query to handle these cases.

We'll demonstrate how to do this with the example below.

Microsoft Access

In this example, we've used the iif function to return 0 if the [Quantity] is 0. Otherwise, it would return the value of [Price] divided by [Quantity]. This is achieved with the following formula:

IIf([Quantity]=0,0,[Price]/[Quantity])

Now, your Access query should no longer return an error when a [Quantity] of 0 is encountered.