|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
- In the Database window, click Tables under Objects, and then click New.
- In the New Table dialog box, click Design View, and then click OK.
- Set the first field name to Dividend, the second to Divisor, and the third to Difference.
- Set the data type to Number for all three fields.
- Save the table with the name tblDivByZero. Do not set a primary key for your table.
- Switch to Datasheet view and enter the following values:
- Close the table.
Create a form and enter the function
- In the Database window, click Forms under Objects, and then click New.
- 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.
- Switch to Design View.
- Set the ControlSource property for the Difference text box to the following function:
- Switch to Form view.
- 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.