ACC: "Type Mismatch in Join expression" Err with Filter by Form

• Microsoft Access versions 7.0, 97

SYMPTOMS

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

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

   Type mismatch in JOIN expression

   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'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 so that they match.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 7.0 and 97. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

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. Using the Form Wizard, 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?"