ACC2000: Ambiguous Error When Running a Make-Table Query (197587)


The information in this article applies to:

  • Microsoft Access 2000
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

  1. 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.
  2. On the Query menu, click Run.
  3. A dialog box will advise you that you are about to paste a number of records into the new table; click Yes.
  4. Save the query as qryTest.
  5. In the Database window, select the newly created table.
  6. On the Edit menu, click Copy.
  7. On the Edit menu, click Paste.
  8. In the Paste Table As dialog box, type tblTest in the Table Name text box.
  9. Click the Structure Only option, and click OK.
  10. Open the tblTest table in Design view.
  11. Change any fields whose data type is AutoNumber to Number.
  12. Close the tblTest table and click Yes in the Save Changes dialog box.

Appending the Records

  1. Open the qryTest query in Design view.
  2. Double-click the join line and select the following property for the join:
       Include ALL records from 'Customers' and only those records from
   'Orders' where the join fields are equal.
  3. On the Query menu, click Append Query.
  4. On the Query menu, click Run.
  5. 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.

MORE INFORMATION

Steps to Reproduce Problem

  1. Open the sample database Northwind.mdb.
  2. Create the following query:
       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): 
       Include ALL records from 'Customers' and only those records from
   'Orders' where the join fields are equal.
  3. On the Query menu, click Make-Table Query.
  4. In the Make-Table dialog box, type TestJoin in the Table Name box, and then click OK.
  5. On the Query menu, click Run.

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

REFERENCES

For more information about query joins, click Microsoft Access Help on the Help menu, type Join types andhow they affect query results in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:6/24/2004
Keywords:kbprb KB197587
©2004 Microsoft Corporation. All rights reserved.