ACC2000: Query Join Type Mismatch Errors Caught at Run Time

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

The mismatch between the data types created by the join generates the error at run time because it is only then that the fields are compared. 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.

Create relationships between tables before you create queries.

Steps To Reproduce the Behavior

The join in the following example is between a Long Integer data type, EmployeeID, and a Text data type, ProductName. To see how Microsoft Access detects a type mismatch error involving joins in a query, follow these steps:

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1.	Start Microsoft Access and open the sample database Northwind.mdb.
2.	In the Database window, click Queries under Objects, and then click New to create a new query in Design view.
3.	In the Show Table dialog box, add the Orders and Products tables, and then click Close.
4.	Drag the EmployeeID field from the Orders field list and drop it onto the ProductName field in the Products field list to create a join between the EmployeeID field in the Orders table and the ProductName field in the Products table.
5.	Add all fields from both tables to the Field row of the query design grid.
6.	On the Query menu, click Run. Note that the query begins to process, and then returns the following error message: Type mismatch in expression. Click OK to clear the error message, or click Help for information about the possible causes of this error.

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

For more information about type mismatches, click Microsoft Access Help on the Help menu, type i'm getting a type mismatch message in the Office Assistant or the Answer Wizard, and then click Search to view the topic.