ACC2000: Query Join Type Mismatch Errors Caught at Run Time (209619)


The information in this article applies to:

  • Microsoft Access 2000
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.

MORE INFORMATION

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.

REFERENCES

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.
Modification Type:MajorLast Reviewed:6/29/2004
Keywords:kberrmsg kbprb KB209619
©2004 Microsoft Corporation. All rights reserved.