Joining two fields that are mismatch by type

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am trying to join two tables together based on a field. It is text in one
field and numeric in another. I cannot change the format of either but I
still want to join them. Is there a way around this?

Chuck
 
Assuming that you need the number field to look like text:

SELECT A.Stuff, B.OtherStuff
FROM A, B
WHERE CStr(A.NumberField) = B.TextField;

The other way around with you needing text to look like a number:

Assuming that you need the number field to look like text:

SELECT A.Stuff, B.OtherStuff
FROM A, B
WHERE IsNumeric(B.TextField) = True
AND A.NumberField = CDbl(B.TextField);

You need to test that the TextField can be evaluated as a number or the CDbl
function will bomb. In fact the above example might not work correctly and
you need to right a subquery first to exclude those records where the
TextField can't be changed to a number.
 
Back
Top