joining 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]

from the table without dashes in SSN, i made a query called AjustedSSN and added a field called AdjustedSSN (plus you can put in all other fields from the table):

AdjustedSSN: Left([SSN],3) & "-" & Mid([SSN],4,2) & "-" & Right([SSN],4)[code]

then in the query where you wanna join the tables, put in the OTHER table (where dashes are already in there) and put in the new query instead of the table that doesnt have dashes in SSN, and join on SSN of table and AdjustedSSN of query.

g