ACC2000: Query Join Type Mismatch Errors Caught at Run Time

This article was previously published under Q209619
This article has been archived. It is offered "as is" and will no longer be updated.
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

Microsoft Access does not detect a type mismatch error involving joinscreated 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 relationshipsbetween 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.
code 13 prb

Article ID: 209619 - Last Review: 01/06/2015 04:13:46 - Revision: 2.0

  • Microsoft Access 2000 Standard Edition
  • kbnosurvey kbarchive kberrmsg kbprb KB209619