ACC: Query Join Type Mismatch Errors Caught at Run Time

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

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

Microsoft Access does not detect a type mismatch error involving joins created in a query until you run the query.

MORE INFORMATION

To illustrate when Microsoft Access detects a type mismatch error involving joins in a query, follow these steps.

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0). You may want to back up the Northwind.mdb (or NWIND.MDB) file and perform these steps on a copy of the database.

1. Open the sample database Northwind.mdb.

2. Create a new query in Design view.

3. In the Add Table dialog box, add the Orders and Products tables, and then click Close.

4. Create a join between the EmployeeID field in the Orders table and the ProductName field in the Products table.

   NOTE: In versions 1.x and 2.0, there is a space in Employee ID and Product Name.

5. Add all fields from both tables to the Field row of the QBE grid.

6. On the Query menu, click Run. Note that the query begins to process, and then returns the following error message:

   In Microsoft Access 7.0 and 97:

         ! Type mismatch in JOIN expression
   

   In Microsoft Access 1.x and 2.0:

         ! Type mismatch
   

   Click OK to clear the error, or click Help for information about the possible causes of this error.

This issue is one reason why it is advantageous to create relationships between tables before you create queries.