Microsoft KB Archive/209619

= ACC2000: Query Join Type Mismatch Errors Caught at Run Time =

Article ID: 209619

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209619



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

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



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



CAUSE
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.



RESOLUTION
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.

 Start Microsoft Access and open the sample database Northwind.mdb. In the Database window, click Queries under Objects, and then click New to create a new query in Design view. In the Show Table dialog box, add the Orders and Products tables, and then click Close. 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. Add all fields from both tables to the Field row of the query design grid.</li> 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.</li></ol>

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

<div class="references_section">