No records returned in query between an Access 2000 table and a linked Access 97 table

No records are returned for a query that joins a Microsoft Access 2000 (Jet 4.0) table to a linked Microsoft Access 97 (Jet 3.5) table. This happens even though you are certain that there are matching records.

This behavior can occur only when all the following conditions are true:

• The query contains an Access 2000 table and a linked Access 97 table.
• The two tables are joined in a one-to-one relationship (Type 1).
• In each table, the linked field has a data type of Text and is indexed.

Instead of a join, use a criteria with the keyword Like that compares one field to the other field in the linked tables. For instance, to correct the behavior in the example in the "Steps to Reproduce Behavior" section later in this article, follow these steps:

1. Highlight the join between the two tables to select it.
2. Press DELETE.
3. In the Criteria field under CustomerID from the Customers table, type the following:
   Like [Customers1].[CustomerID]

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

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.

Steps to Reproduce Behavior

1. Place an unchanged copy of the sample database Northwind.mdb that is included with Access 97 on your desktop. Do not convert it to Access 2000.
2. In Access 2000, open the sample database Northwind.mdb that is included with Access 2000.
3. On the File menu, point to Get External Data, and then click Link Tables
4. In the Link dialog box, browse to the Access 97 version of the sample database Northwind.mdb, click Northwind.mdb, and then click Link.
5. In the Link Tables dialog box, click Categories and Customers, and then click OK. Note that two new linked tables, Categories1 and Customers1, appear in the Database window.
6. Create a new query that includes both the Customers table from the sample database Northwind.mdb 2000 and the linked Customers1 table. Note that the tables are automatically joined on the CustomerID field (Primary Key in both, and Text in both).
7. Drag the CustomerID field from both tables to the query design grid.
8. Run the query.
   
   Note that no records are returned.
9. Create a second query that includes the Categories table from the sample database Northwind.mdb 2000 and the linked Categories1 table. Note that the tables are automatically joined on the CategoryID field (Indexed in both, and Numeric in both).
10. Drag the CategoryID field from both tables to the query grid.
11. Run the query.
    
    Note that all records are returned. In this case, because the joined fields are a Number data type instead of a Text data type, records are returned.