join on text fields with different formats...

[AWEinCA]

I have two (2) tables that I want to JOIN together:

One (1) table has SSN formatted as "111223333"
The other table has SSN formatted as "111-22-333"

These are not technically equal. How do I JOIN these two (2) tables on SSN?

I have tried but it doesn't work:
SELECT ....
FROM ...
WHERE (left(c.SSN,3) & " - " & Mid(c.SSN,4,2) & " - " & right(c.SSN,3)) = b.SSN

THanks in advance for ANY help you can give,
Aaron

 

[GingerR]

see response in Queries forum