Microsoft KB Archive/96581

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

Article ID: 96581

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q96581





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.

 Open the sample database Northwind.mdb. Create a new query in Design view. In the Add Table dialog box, add the Orders and Products tables, and then click Close. 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. Add all fields from both tables to the Field row of the QBE grid. 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.</li></ol>

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.

Additional query words: code 13

Keywords: kberrmsg kbinfo KB96581

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.