Microsoft KB Archive/178112

= ACC: "Type Mismatch in Join Expression" Error with Filter by Form =

Article ID: 178112

Article Last Modified on 1/22/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q178112



Novice: Requires knowledge of the user interface on single-user computers.



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 Lookup field's row source. If the list box or 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 this to be a problem in Microsoft Access 7.0 and 97.



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   Add the following records to Table1:

       Id        Name ---

10001    Apples 10002    Pears 10003    Oranges   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 Table2.</li> Use the Form Wizard to create a new form based on Table2 and add the Test Field.</li> Click Finish.</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 Table2 and changing the Test data type to Number resolves the problem.</li></ol>

<div class="references_section">