Type Mismatch in Expression Error

  • Thread starter Thread starter rhett83159
  • Start date Start date
R

rhett83159

I'm trying to run a query in Access 2000 using linked tables. When I
execute it I get the following error message: Type Mismatch in
Expression Error. I know where the error lies because the two fields I'm
joining on are different data types. One is a text field that contains
nothing but numerics and the other is a true numeric field.

Is there anyway around this without having to import the entire table
and changing the field's data type?

Thanks for your help.
 
You can build the SQL in the SQL window but not in the query grid.
Switch to the SQL (text) window and do something like

SELECT <Your Field List>
FROM TableA INNER JOIN TableB
ON TableA.NumberField = CDbl(TableB.TextField)
WHERE <Criteria>

If your numbers never have a decimal portion, then you can probably use CDBL to
better effect instead of CLNG.

WARNING: If the textfield is null or has invalid data this is likely to give you errors.
 
Back
Top