ACC: "Type Mismatch in Join Expression" Error with Filter by Form (178112)


The information in this article applies to:

  • Microsoft Access for Windows 95 7.0
  • Microsoft Access 97
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.

MORE INFORMATION

Steps to Reproduce Problem


  1. 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
  2. Add the following records to Table1: 
            Id        Name
        ---------------

        10001     Apples
        10002     Pears
        10003     Oranges
  3. Create another table called Table2 with the following properties: 
            Table: Table2
        ---------------------
           Field Name: Test
              Data Type: Text
  4. On the Lookup tab, change the following properties: 
            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"
  5. Save and close Table2.
  6. Use the Form Wizard to create a new form based on Table2 and add the Test Field.
  7. Click Finish.
  8. On the Records menu, point to Filter, and then click Filter By Form.
  9. Select one of the names from the combo box for Test.
  10. 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.

REFERENCES

For more information about Filter By Form or Filter By Selection, search the Help Index for "Filter By Form" or "Filter By Selection," or ask the Microsoft Access 97 Office Assistant.

For more information about Lookup fields, ask the Microsoft Access 97 Office Assistant "What is a Lookup field?"
Modification Type:MajorLast Reviewed:10/20/2003
Keywords:kbbug KB178112
©2003 Microsoft Corporation. All rights reserved.