Relating linked data - Datatype mismatches

J

John F

I have several files that link to several different ODBC
sources. Many times when I make joins to match the data
the one database has different formats than another.

For instance, AccountNumber from database A might is
001234567 (text) and AccountNumber from database B is
1234567 (long integer).

Is there any "easy" way to reconcile this without
constantly creating interim tables?

Thanx!

John
 
R

Roger Carlson

You might try using a conversion function in your JOIN expression:
FROM
DatabaseA INNER JOIN DatabaseB ON clng(DatabaseA.AccountNumber) =
DatabaseB.AccountNumber

or
FROM
DatabaseA INNER JOIN DatabaseB ON DatabaseA.AccountNumber =
Format(DatabaseB.AccountNumber,"000000000")

I don't know if this will work. It's off the top of my head, but it's worth
a shot.
 
Top