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


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q287709
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.
Modification Type:MajorLast Reviewed:11/5/2003
Keywords:kbbug kberrmsg kbpending KB287709
©2003 Microsoft Corporation. All rights reserved.