ACC: "Type Mismatch in Join Expression" Error 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 perform 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 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?"

Additional query words: dropdown drop pra filtered filtering problems

Keywords : FmsProp
Version : WINDOWS:7.0,97
Platform : WINDOWS
Issue type : kbbug