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

ID: Q202269

The information in this article applies to:
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).


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 this to be a problem in the Microsoft products listed at the beginning of this article.


MORE INFORMATION

Steps to Reproduce Problem

  1. In a new database, create a new table called Table1 with the following properties:


    2. 
   Table: Table1
   --------------------
   Field Name: Id
      Data Type: Number

   Field Name: Name
      Data Type: Text
  2. Open the Table1 table in Datasheet view and, when prompted to save, click Yes. When prompted about the primary key, click No.


  3. Add the following records to Table1:


    4. 
   Id        Name
   ---------------

   10001     Apples
   10002     Pears
   10003     Oranges
  4. Create another table called Table2 with the following properties:


    5. 
   Table: Table2
   ---------------------
   Field Name: Test
      Data Type: Text
  5. On the Lookup tab, change the following properties:


    6. 
   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"
  6. Save and close the Table2 table and, when prompted to create a Primary Key, click No.


  7. Use the Form Wizard to create a new form based on the Table2 table and add the Test Field.


  8. Click Finish.


  9. On the View menu, click Form View.


  10. On the Records menu, point to Filter, and then click Filter By Form.


  11. Select one of the names from the combo box for Test.


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



REFERENCES

For more information about Filter By Form, click Microsoft Access Help on the Help menu, type "form, filter" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about Lookup fields, click Microsoft Access Help on the Help menu, type "Work with lookup fields" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Additional query words: dropdown drop pra filtered filtering problems 


Keywords          : kberrmsg kbdta FmsProp 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbbug

Last Reviewed: May 18, 1999