Avoiding divide by zero errors in Access

Applies to
Microsoft Office Access 2003
Microsoft Access 97, 2000 and 2002

If you try to divide a number by zero in Access, you receive a #DIV/0 error if you are in a form or report, or a #ERROR error if you are in a query. To avoid a divide by zero error, you can use the IIF function to set the results field to a zero-length string ("") if the divisor in a calculation is zero. You can then search the field for any values that contain a zero-length string and flag the results. The following steps demonstrate this technique by creating a table and a bound form, inserting three text boxes, and entering an expression.

Create a table with values

  1. In the Database window, click Tables under Objects, and then click New.
  2. In the New Table dialog box, click Design View, and then click OK.
  3. Set the first field name to Dividend, the second to Divisor, and the third to Difference.
  4. Set the data type to Number for all three fields.
  5. Save the table with the name tblDivByZero. Do not set a primary key for your table.
  6. Switch to Datasheet view and enter the following values:
Dividend Divisor Difference
10 5 0
20 0 0
30 10 0
  1. Close the table.

Create a form and enter the function

  1. In the Database window, click Forms under Objects, and then click New.
  2. In the New Form dialog box, click AutoForm: Columnar, click tblDivByZero in the Choose the table or query where the object's data comes from box, and then click OK.
  3. Switch to Design View.
  4. Set the ControlSource property for the Difference text box to the following function:
=IIf([Divisor]=0,Null,[Dividend]/[Divisor])
  1. Switch to Form view.
  2. Using the record selector at the bottom of the form, scroll though each record.
    The Difference text box displays a value for all records whose divisor was not zero. For the record that had zero as the divisor, the Difference text box contains a Null value.
 
 
Applies to:
Access 2003