Microsoft KB Archive/197587

= ACC2000: Ambiguous Error When Running a Make-Table Query =

Article ID: 197587

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q197587



Novice: Requires knowledge of the user interface on single-user computers.



SYMPTOMS
When you run a make-table query that has an outer join between two tables, you may receive the following error message:

You tried to assign the Null value to a variable that is not a Variant data type.

The error message does not clearly state the exact problem with the query design.



CAUSE
The query is trying to populate a table where a field contains a Null value and the data type is AutoNumber.



RESOLUTION
Use either of the following methods to work around this behavior.

Method 1
If you do not need the data returned by the AutoNumber field, omit that field from the query.

Method 2
By modifying your existing query, you can create a new table and use an append query to populate the table. To do so, follow these steps.

Creating a Table with the Same Structure
  After clicking OK on the error message described in the "Symptoms" section, the query will be in Design view. Double-click the join line and select the following property for the join:   Only include rows where the joined fields from both tables are equal.  On the Query menu, click Run. A dialog box will advise you that you are about to paste a number of records into the new table; click Yes. Save the query as qryTest. In the Database window, select the newly created table.</li> On the Edit menu, click Copy.</li> On the Edit menu, click Paste.</li> In the Paste Table As dialog box, type tblTest in the Table Name text box.</li> Click the Structure Only option, and click OK.</li> Open the tblTest table in Design view.</li> Change any fields whose data type is AutoNumber to Number.</li> Close the tblTest table and click Yes in the Save Changes dialog box.</li></ol>

Appending the Records
<ol> Open the qryTest query in Design view.</li>  Double-click the join line and select the following property for the join: <pre class="fixed_text">  Include ALL records from 'Customers' and only those records from 'Orders' where the join fields are equal. </li> On the Query menu, click Append Query.</li> On the Query menu, click Run.</li> A dialog box will advise you that you are about to paste a number of records into the new table; click Yes.

Note that the tblTest table now contains the desired records.</li></ol>

<div class="moreinformation_section">

Steps to Reproduce Problem
<ol> Open the sample database Northwind.mdb.</li>  Create the following query: <pre class="fixed_text">  Query: MakeOrders -  Type: Make Table Query Join: [Customers].[CustomerID] ---> [Orders].[CustomerID]

Field: Orders.* Table: Orders NOTE: Make sure that the following property is selected for the join (double-click the join line to check the join property): <pre class="fixed_text">  Include ALL records from 'Customers' and only those records from 'Orders' where the join fields are equal. </li> On the Query menu, click Make-Table Query.</li> In the Make-Table dialog box, type TestJoin in the Table Name box, and then click OK.</li> <li>On the Query menu, click Run.

Note that you receive the error message described in the "Symptoms" section.</li></ol>

<div class="references_section">