ACC2000: "Type Mismatch in Join expression" with Filter By Form

When you use a combo box or list box control to perform a Filter By Form, you may receive the following error message followed by:

The control is bound to a field that is of a different data type than the bound column in the row source of the Lookup field. If the list box or the combo box has a control source, it should be of the same data type as the fields populating the list.

Change the data type of either the control source or the bound column of the Lookup field so that they match.

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

Steps to Reproduce Problem

1. In a new database, create a new table called Table1 with the following properties:

      Table: Table1
      --------------------
      Field Name: Id
         Data Type: Number
   
      Field Name: Name
         Data Type: Text
   					

2. Open the Table1 table in Datasheet view and, when prompted to save, click Yes. When prompted about the primary key, click No.
3. Add the following records to Table1:

      Id        Name
      ---------------
   
      10001     Apples
      10002     Pears
      10003     Oranges
   					

4. Create another table called Table2 with the following properties:

      Table: Table2
      ---------------------
      Field Name: Test
         Data Type: Text
   					

5. On the Lookup tab, change the following properties:

      Display Control - Combo Box
      Row Source Type - Table/Query
      Row Source - Table1
      Bound Column - 1
      Column Count - 2
      Column Heads - No
      Column Widths - 0";1"
   					

6. Save and close the Table2 table and, when prompted to create a Primary Key, click No.
7. Use the Form Wizard to create a new form based on the Table2 table and add the Test Field.
8. Click Finish.
9. On the View menu, click Form View.
10. On the Records menu, point to Filter, and then click Filter By Form.
11. Select one of the names from the combo box for Test.
12. On the Filter menu, click Apply Filter/Sort.
    
    Note that you receive the error message described in the "Symptoms" section. In this case, switching to Design view in the Table2 table and changing the Test data type to Number resolves the problem.

For more information about Filter By Form, click Microsoft Access Help on the Help menu, type form, filter in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about Lookup fields, click Microsoft Access Help on the Help menu, type Work with lookup fields in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.