ACC: Query Join Type Mismatch Errors Caught at Run Time (96581)

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.

It may not be obvious that the join is at fault because of the timing of the error message, especially if you are querying large amounts of data over a network. The join in this example is between a Long Integer data type, EmployeeID, and a Text data type, ProductName. The mismatch between the data types in the join is what generates the error at run time.

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