Importing SSN from Excel To Access Changes Format

[Guest]

I'm importing SSN data from an Excel spreadsheet into an Access 2003 table.
The spreadsheet has the SSN in the General format. I need to convert this to
a text format showing the SSN as '000-00-0000', which I can do in Excel, but
Access reformats to '000000000' and drops leading zeroes. I need the other
format in order to run an unmatched query against a second table whose SSN's
are formatted with the dashes. I can't change that format as it comes from
data imported from a custom SQL app. Any ideas?

 

[Guest]

format(txtSSN,"000-00-0000")

 

[Guest]

Thanks for the suggestion. It didn't work, but I'll keep looking!

 

[Guest]

Melissa,
I tested it, it does work. I tried it with text:
x="000320123"
?format(x,"000-00-0000")
result is 000-32-0123
I tried it with a number:
x=320123
?format(x,"000-00-0000")
result is 000-32-0123

I don't understand what the problem is here. Try the above in your
Immediate window and you will see the same results. Also, have you looked at
the data you are comparing against to see it is in 000-00-0000 format. Have
you checked for leading or trailing spaces. There has to be another issue
here, I have been using that formatting for SSNs for years.