ACC: Append Query Causes Divide Error

ID: Q177102

The information in this article applies to:

Microsoft Access versions 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you run an append query, you may receive one of the following error messages:

Microsoft Windows 95

This program has performed an illegal operation and will be shut down. If the problem persists, contact the program vendor.

If you are using Microsoft Access 7.0, you receive the following message when you click Details:

MSACCESS caused a divide error in module MSJT3032.DLL

If you are using Microsoft Access 97, you receive the following message when you click Details:

MSACCESS caused a divide error in module MSJET35.DLL

Microsoft Windows NT

An application error has occurred and an application error log is being generated.

MSACCESS.exe Exception: divide by zero

CAUSE

The append query contains a join to a field in another query whose UniqueValues property is set to Yes. For example, the append query may be based on a table that is joined to a select query whose UniqueValues property is set to Yes in order to limit the data that is appended.

WORKAROUND

When creating an append query, do not use a join to a query whose UniqueValues property is set to Yes. Set the UniqueValues property of the select query to No, and then set the UniqueValues property of the append query to Yes. Instead of using a select query, you may also want to consider using the source table of the select query, and then setting the UniqueValues property of the append query to Yes.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft Access versions 7.0 and 97.

MORE INFORMATION

Steps to Reproduce Behavior

Create the Tables

Create a new database in Microsoft Access.

Create the following table and save it as tblDivideError1:


      Table: tblDivideError1
      ---------------------------
      Field Name: ID
         Data Type: Number
         Field Size: Long Integer
      Field Name: TextName
         Data Type: Text
         Field Size: 50

      Table Properties: tblDivideError1
      ---------------------------------
      PrimaryKey: ID

   Close and save the table.

Create a copy of the tblDivideError1 table. To create a copy of the tblDivideError1 table, select it in the Database window; press CTRL+C, and then press CTRL+V. In the Paste Table As dialog box, type tblDivideError2 in the Table Name box, and then click OK.

Open the tblDivideError1 table and add the following data:


      ID     TextName
      --     --------
      1      Test
      2      Test
      3      Test
      4      Test
      5      Test

Create the following table and save it as tblDivideError3:


      Table: tblDivideError3
      ---------------------------
      Field Name: ID
         Data Type: Number
         Field Size: Long Integer
      Field Name: AltID
         Data Type: Number
         Field Size: Long Integer

      Table Properties: tblDivideError3
      ---------------------------------
      PrimaryKey: ID

Close and save the table.

Open the tblDivideError3 table and add the following data:


      ID     AltID
      --     -----
      1      2
      2      3
      3      5
      4      5

Create the Queries

Create a new query based on the tblDivideError3 table and save it as qryDivide1:


      Query: qryDivide1
      -------------------------
      Type: Select Query

      Field: AltID
         Table: tblDivideError3

      Query Properties
      ------------------
      Unique Values: Yes

Create a new query and add both the tblDivideError1 table and qryDivide1 query. Save it as qryDivideAppend:


      Query: qryDivideAppend
      ---------------------------------------------
      Type: Append Query
      Join: tblDivideError1.ID <-> qryDivide1.AltID

      Field: ID
         Table: tblDivideError1
      Field: AltID
         Table: qryDivide1

      Query Properties
      ----------------------------------
      Destination Table: tblDivideError2

Run qryDivideAppend. Note that you receive the error described in the "Symptoms" section.

REFERENCES

For more information about the UniqueValues property, search the Help Index for "UniqueValues property."

Additional query words: crash fail

Keywords : QryAppnd
Version : WINDOWS:7.0,97
Platform : WINDOWS
Issue type : kbbug