Help Importing to Access 2003 SSNs from Excel 2003

[Guest]

I am trying (and failing) to import Social Security Numbers from an Excel
2003 worksheet to a Access 2003 table. I am having problems any time the
Social Security Number has a leading zero(s).

What am I missing? (Surely this data should be exchangable between office
products.)

Thanks.

 

[Guest]

The data can be exchanged; however, you have to take steps the ensure you get
the correct data type. When Access imports from Excel, it examines the data
in the columns and makes its own decisioin on what data type to use. Since
it appears to Access that SSNs are numeric, it make the column numeric and
numeric data ignores any leading zeros.

It sounds like you are allowing the import to create a table. If you,
instead, create a table with defined data types and import to the existing
table, the problem will go away.

 

[Guest]

Unfortunately, I've already tried that. It appears that the Excel data is a
number that displays in a social security format, but when I try to import it
to Access that program only will give Social Security as a format in a text
field. Even with an input mask, the best I can do is populate the field from
right to left, so that there are blanks where leading zeros should be. That
is not a viable solution.

Looking to display all the digits of the Social Security number even whe it
begin with zero(s).

Thanks for your response.

 

[Guest]

An input mask has no effect on data presented, it is only used in data entry
to ensure the user enters correct data. You could try using a format like
"000-00-0000"

 

[Guest]

Thanks for your suggestion. Looking at the Excel file, the format field for
SSN is a special one, so that ssn's that are entered as 123456 display as
000-12-3456. When I import that field into Access, it doesn't import the
displayed value, but the entered one, so the text field ends up as 123-45-6.
If I use the input mask in a text field and an ! in front of the format, the
data looks like -12-3456. There doesn't seem to be a way to format the field
so that if the string is less than nine characters, it will add zeros to the
front until the string is nine characters long.
I tried changing the format field, but that didn't work for me.

You would think that Access and Excel would compliment each other on the
Social Security number format, but apparently not.

The only suggestion around here is to save the Excel file as a text file
first, and then import that file. I am stubborn enough to believe you
shouldn't have to do this.

Thanks again

 

[Guest]

I would agree, Wayne, but that is the way it is. I really don't have another
suggestion for you.

 

[Guest]

Wayne,

Using Access 2003, when I set the SSN field to datatype = Text, and length =
11, i am able to import from Excel into Access, and it retains the leading
zeros and hyphens(when they are enter). Then, you can strip out the hyphens
using the Replace function.

Does that technique address your issue?

Dale