Microsoft KB Archive/202269

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

Article ID: 202269

Article Last Modified on 6/30/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q202269





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 2002 version of this article, see 287709.



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

Type mismatch in JOIN expression

followed by:

Microsoft Access didn't apply the filter.



CAUSE
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.



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



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 Problem
  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  Open the Table1 table in Datasheet view and, when prompted to save, click Yes. When prompted about the primary key, click No.  Add the following records to Table1:   Id        Name ---

10001    Apples 10002    Pears 10003    Oranges </li>  Create another table called Table2 with the following properties: <pre class="fixed_text">  Table: Table2 -  Field Name: Test Data Type: Text </li>  On the Lookup tab, change the following properties: <pre class="fixed_text">  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" </li> Save and close the Table2 table and, when prompted to create a Primary Key, click No.</li> Use the Form Wizard to create a new form based on the Table2 table and add the Test Field.</li> Click Finish.</li> On the View menu, click Form View.</li> On the Records menu, point to Filter, and then click Filter By Form.</li> Select one of the names from the combo box for Test.</li> 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.</li></ol>

<div class="references_section">