Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: Ambiguous Error When Running a Make-Table Query

ACC2000: Ambiguous Error When Running a Make-Table Query

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


↑ Back to the top

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.

↑ Back to the top

Cause

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

↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

Keywords: kbprb, kb, kbarchive

↑ Back to the top

Article Info
Article ID : 197587
Revision : 1
Created on : 1/7/2017
Published on : 6/24/2004
Exists online : False
Views : 386