ACC2002: "Type Mismatch in Join Expression" Applying Filter By Form

This article was previously published under Q287709
This article has been archived. It is offered "as is" and will no longer be updated.
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

For a Microsoft Access 2000 version of this article, see 202269.
SYMPTOMS
When you use a combo box or a list box to perform a Filter By Form, you may receive one of the following error messages:
Type mismatch in expression.
-or-
Type mismatch in JOIN expression
Followed by:
Microsoft Access didn't apply the filter.

Microsoft Access may not be able to apply the filter if you entered an invalid data type in one of the fields.
Do you want to close the filter anyway?

If you click Yes, Microsoft Access will build the filter, but won't apply it to the recordset.
Then it will close the Filter By Form window.
CAUSE
The control is bound to a field with a different data type than the bound column in the row source of the Lookup field. If the combo box or list box has a value assigned to its ControlSource property, it should be of the same data type as the fields populating the list.
RESOLUTION
Change the data type of either the control source or the bound column of the Lookup field to match the correct fields.
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
MORE INFORMATION

Steps to Reproduce the Behavior

  1. In a new database, create a new table with the following property assignments, and name it Table1:
       Table: Table1   -----------------   Field Name: Id   Data Type: Number   Field Name: Name   Data Type: Text					
  2. Open the Table1 table in Datasheet view, and when you are prompted to save it, click Yes. When you are prompted for a primary key, click No.
  3. Add the following records to the Table1 table:
       Id        Name   -----------------   10001     Apples   10002     Pears   10003     Oranges					
  4. Create another table with the following property assignments, and name it Table2:
       Table: Table2   ----------------   Field Name: Test   Data Type: Text					
  5. On the Lookup tab, set the following properties for the Test field:
       Display Control: Combo Box   Row Source Type: Table/Query   Row Source: Table1   Bound Column: 1   Column Count: 2   Column Widths: 0";1"					
  6. Close and save the Table2 table. When you are prompted to create a primary key, click No.
  7. Use the Form Wizard to create a new form that is based on the Table2 table, add the Test field, and then click Finish.
  8. In Form view of the new form, point to Filter on the Records menu, and then click Filter By Form.
  9. Select one of the names from the Test combo box.
  10. On the Filter menu, click Apply Filter/Sort.
Note that you receive one of the the error messages that is mentioned in the "Symptoms" section of this article. In this case, switching to Design view in the Table2 table and changing the Test field data type to Number resolves the issue.
REFERENCES
For more information about Filter By Form, click Microsoft Access Help on the Help menu, type about filters in the Office Assistant or the Answer Wizard, and then click Search to view the "About Filters" topic.

For more information about Lookup fields, click Microsoft Access Help on the Help menu, type lookup fields in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
drop dropdown filtered filtering pra problems OfficeErrorMessages 10026
Properties

Article ID: 287709 - Last Review: 12/06/2015 00:22:22 - Revision: 3.0

Microsoft Access 2002 Standard Edition

  • kbnosurvey kbarchive kberrmsg kbbug kbpending KB287709
Feedback