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

Article translations Article translations
Article ID: 287709 - View products that this article applies to.
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.
Expand all | Collapse all

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.

Properties

Article ID: 287709 - Last Review: November 5, 2003 - Revision: 3.0
APPLIES TO
  • Microsoft Access 2002 Standard Edition
Keywords: 
kberrmsg kbbug kbpending KB287709

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com