PRB: "Data Type Mismatch" with Linked Tables in Query

• Microsoft Visual FoxPro for Windows, versions 3.0 and 3.0b

SYMPTOMS

One of the most common causes of the "Operator/Operand type mismatch" error when running a query is that the joining fields between the tables are of different data types. The field from each table that joins the tables for the query must have identical data types. The exception is when one field is numeric and the other is character. FoxPro will attempt to make the character field numeric by using the VAL() function. Of course this is only useful if the character field holds numbers only. If there are any other types of characters in the field that FoxPro uses the VAL() function on, the query result will contain no records.

CAUSE

The joining fields in the two tables are not of the same data type.

STATUS

This behavior is by design. Choosing the correct data type when joining tables for a query is a necessity if the correct information is to be retrieved.

MORE INFORMATION

When using the Query designer to join information from tables into a query, FoxPro will look for the first two fields that have the same name in both tables regardless of their data type. If there is no field in each table that matches, then the user has to select the joining fields. If two fields with different data types are chosen to join the tables, then the error will occur because FoxPro cannot match the data in the fields correctly.

Steps to Reproduce Behavior

1. On the File menu, click New. Then click Query, and click New File to create a new query.

2. Choose a table from the Add Table box. On the Query menu, click Add Table, and add another table.

3. In the Join Condition dialog box, ensure that the joining field's data type on the left doesn't match the data type of the joining field on the right. Also make sure that the types are dissimilar; that is make one a character data type and the other a numeric data type. Other possible combinations are date-character, logical-numeric, and so on. Click OK to close the Join Condition dialog box.

4. click the Fields tab of the Query Designer, and add some fields to the Selected Output list box on the right.

5. Run the query, and note the "Operator/Operand type mismatch" error.