Microsoft KB Archive/177102

= ACC: Append Query Causes Divide Error =

Article ID: 177102

Article Last Modified on 1/22/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q177102



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



SYMPTOMS
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.



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: <pre class="fixed_text">     ID     TextName --          1      Test 2     Test 3     Test 4     Test 5     Test </li>  Create the following table and save it as tblDivideError3: <pre class="fixed_text">     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. </li>  Open the tblDivideError3 table and add the following data: <pre class="fixed_text">     ID     AltID --    -      1      2      2      3      3      5      4      5                    </li></ol>

Create the Queries
<ol>  Create a new query based on the tblDivideError3 table and save it as qryDivide1: <pre class="fixed_text">     Query: qryDivide1 -     Type: Select Query

Field: AltID Table: tblDivideError3

Query Properties --     Unique Values: Yes > </li>  Create a new query and add both the tblDivideError1 table and qryDivide1 query. Save it as qryDivideAppend: <pre class="fixed_text">     Query: qryDivideAppend -     Type: Append Query Join: tblDivideError1.ID <-> qryDivide1.AltID

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

Query Properties --     Destination Table: tblDivideError2 </li> Run qryDivideAppend. Note that you receive the error described in the "Symptoms" section.</li></ol>

<div class="references_section">