Microsoft KB Archive/287709

= ACC2002: &quot;Type Mismatch in Join Expression&quot; Applying Filter By Form =

Article ID: 287709

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



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.



Steps to Reproduce the Behavior
  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  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.  Add the following records to the Table1 table:   Id        Name -  10001     Apples 10002    Pears 10003    Oranges </li>  Create another table with the following property assignments, and name it Table2: <pre class="fixed_text">  Table: Table2 Field Name: Test Data Type: Text </li>  On the Lookup tab, set the following properties for the Test field: <pre class="fixed_text">  Display Control: Combo Box Row Source Type: Table/Query Row Source: Table1 Bound Column: 1 Column Count: 2 Column Widths: 0&quot;;1&quot; </li> Close and save the Table2 table. When you are prompted to create a primary key, click No.</li> Use the Form Wizard to create a new form that is based on the Table2 table, add the Test field, and then click Finish.</li> In Form view of the new form, point to Filter on the Records menu, and then click Filter By Form.</li> Select one of the names from the Test combo box.</li> On the Filter menu, click Apply Filter/Sort.</li></ol>

Note that you receive one of the the error messages that is mentioned in the &quot;Symptoms&quot; 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.

<div class="references_section">