SSN format

[belsha]

I have tried various suggestions posted, but still can't seem to get the result I need. I have a field I have to import to a database, SS (Social Security Number). In the database I am exporting from, it is text and does not use leading zeros. In the database I am importing into, it must have leading zeros and be in the format 123-45-6789 as a text field. I have tried input masks, formats, etc but still can't seem to make it work. I do not want to add zeros to fields that have the beginning three digits, or add too many zeros to the fields having two of the three digits. Any help would be appreciated.

 

[andyski101]

OK, well, you might want to try using VBA

This is off the top of my head so there might be some error, I'll test it when I can, but I think its right.

y=len(strSSN)
for x=9-y
    strSSN="0" & strSSN
next x

 

[belsha]

I have no form to use the code in, just a table with raw data

 

[andyski101]

Then you might want to copy to excel, make your changes there with a marco or something like that, and then paste into the new database.

If your going to have to do this import often, you might want to consider creating a form to automate the import.

 

[Pat Hartman]

SSN should be stored as text so that it retains the leading zeros. If necessary, you can get them back by using the Format() function:

Format(SSN, "000-00-0000")