ACC: Query Join Type Mismatch Errors Caught at Run Time

ID: Q96581

The information in this article applies to:

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.

  1. Open the sample database Northwind.mdb.


  2. Create a new query in Design view.


  3. In the Add Table dialog box, add the Orders and Products tables, and then click Close.


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


  5. Add all fields from both tables to the Field row of the QBE grid.


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


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 QryJoin 
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbinfo

Last Reviewed: March 20, 1999