Formatting Social Security Numbers in Table Design View

[W L]

Windows XP
Office XP

Total access newbie here, so I hope I phrase this question correctly.

I import data into a table as a text file and once imported, one of the
columns contains social security numbers. I know in design view i can tell
access to format the numbers the way i need but i need a bit of help. In
excel I used an if then statement in a macro. Here is the statement:

=IF(RC[-2]<100000000,""0""&LEFT(RC[-2],2)&""-""&LEFT(RC[-1],2)&""-""&RIGHT(R
C[-1],4),IF(RC[-2]>699999999,LEFT(RC[-2],2)&""-""&RIGHT(RC[-2],7),LEFT(RC[-2
],3)&""-""&LEFT(RC[-1],2)&""-""&RIGHT(RC[-1],4)))

Can anyone offer a suggestion for me?

Thanks,
Wendy

 

[John Vinson]

Windows XP
Office XP

Total access newbie here, so I hope I phrase this question correctly.

I import data into a table as a text file and once imported, one of the
columns contains social security numbers. I know in design view i can tell
access to format the numbers the way i need but i need a bit of help. In
excel I used an if then statement in a macro. Here is the statement:

=IF(RC[-2]<100000000,""0""&LEFT(RC[-2],2)&""-""&LEFT(RC[-1],2)&""-""&RIGHT(R
C[-1],4),IF(RC[-2]>699999999,LEFT(RC[-2],2)&""-""&RIGHT(RC[-2],7),LEFT(RC[-2
],3)&""-""&LEFT(RC[-1],2)&""-""&RIGHT(RC[-1],4)))

Can anyone offer a suggestion for me?

It's a mite easier here:

Format([SSN], "000\-00\-0000")

or simply set the Format property of the field to the same string.

Note that Access (unlike Excel) has "strong data typing" - the SSN
field in your table should be of Text datatype, not numeric. This will
prevent problems with leading zeros (and you'll never be doing math
with SSN's anyway).

 

[W L]

I appreciate your taking the time to respond. My problem with simply
formatting the SSN as a custom 000-00-0000 is that it does not take into
account the fact that some of the ID numbers are not in fact, SSN's but are
Tax ID numbers, which are hyphenated after the second digit and then not
again. (ssn: 123-45-6789, tax id: 12-3456789). Importing it as a text string
instead of an integer solves my leading zeros problem, but i still have the
problem remaining of: if the imported field is greater than 699999999, it
should not be formatted the same as a ssn but as an entity number, with only
one hyphen placed after the second digit.

Windows XP
Office XP

Total access newbie here, so I hope I phrase this question correctly.

I import data into a table as a text file and once imported, one of the
columns contains social security numbers. I know in design view i can tell
access to format the numbers the way i need but i need a bit of help. In
excel I used an if then statement in a macro. Here is the statement:

=IF(RC[-2]<100000000,""0""&LEFT(RC[-2],2)&""-""&LEFT(RC[-1],2)&""-""&RIGHT(

R

C[-1],4),IF(RC[-2]>699999999,LEFT(RC[-2],2)&""-""&RIGHT(RC[-2],7),LEFT(RC[-

2

],3)&""-""&LEFT(RC[-1],2)&""-""&RIGHT(RC[-1],4)))

Can anyone offer a suggestion for me?

It's a mite easier here:

Format([SSN], "000\-00\-0000")

or simply set the Format property of the field to the same string.

Note that Access (unlike Excel) has "strong data typing" - the SSN
field in your table should be of Text datatype, not numeric. This will
prevent problems with leading zeros (and you'll never be doing math
with SSN's anyway).

 

[John Vinson]

I appreciate your taking the time to respond. My problem with simply
formatting the SSN as a custom 000-00-0000 is that it does not take into
account the fact that some of the ID numbers are not in fact, SSN's but are
Tax ID numbers, which are hyphenated after the second digit and then not
again. (ssn: 123-45-6789, tax id: 12-3456789). Importing it as a text string
instead of an integer solves my leading zeros problem, but i still have the
problem remaining of: if the imported field is greater than 699999999, it
should not be formatted the same as a ssn but as an entity number, with only
one hyphen placed after the second digit.

Ah! Sorry, didn't realize (because you didn't mention and I didn't
dissect the EXCEL code) that there were two separate formats.

You'll want to use an UPDATE query, I'd guess; use a criterion of

LIKE "#########"

to select only those records without hyphens, and Update To

IIF(Left([SSN], 1) > 6, Format([SSN], "00\-0000000"), Format([SSN],
"000\-00\-0000"))