Type Mismatch in Expression Error

[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.

 

[John Spencer (MVP)]

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.