ACC97: Ambiguous Error When Running a Make-Table Query

• Microsoft Access 97

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.

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

Method 2

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