ACC97: Ambiguous Error When Running a Make-Table Query

ID: Q187337

The information in this article applies to:

SYMPTOMS

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

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 isn't 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 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.*
         Tables: Customers and 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, search the Help Index for "joins, overview," click "Join types and how they affect query results" from the Topics Found dialog box, or ask the Office Assistant.

Additional query words: error 3162 


Keywords          : QryMktbl 
Version           : WINDOWS:97
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: April 27, 1999