Is this type of Join Possible?

[nafflerbach]

I have a need to match records from two separate tables and the like fields
are not identical. One table contains 6 unique digits of an account number
and the other contains the entire 12 digit number. I cannot delete the other
digits to leave the unique digits. Is there a way to join these two tables
using these fields?

EX. 1) 123456 2) ###123456###

 

[Jeff Boyce]

Are you saying that if you had a way to take ONLY the six middle-most
characters in the longer field, you'd be able to do the match?

If so, take a look at using the Mid() function in a query to derive the
middle-most characters, then create another query that joins the first one
with your other table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Golfinray]

Yes, the Mid function will work. Mid([yourfieldname],starting number,howmany
numbers), Like mid([yourfield],3,5) starts at the 3rd position and takes the
next 5 characters.

 

[John W. Vinson]

I have a need to match records from two separate tables and the like fields
are not identical. One table contains 6 unique digits of an account number
and the other contains the entire 12 digit number. I cannot delete the other
digits to leave the unique digits. Is there a way to join these two tables
using these fields?

EX. 1) 123456 2) ###123456###

As John and Golfinray suggest, the Mid() function should help... if the six
digits are at a predictable location. However, are *these* potential matches?

123456ABCDEF
XYZ123456PQR
MNOPQ123456R

or even
121234561234