Formatting Social Security Numbers in Table Design View

  • Thread starter Thread starter W L
  • Start date Start date
W

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
 
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?
Click to expand...

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).
 
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.


John Vinson said:
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(
Click to expand...
R
C[-1],4),IF(RC[-2]>699999999,LEFT(RC[-2],2)&""-""&RIGHT(RC[-2],7),LEFT(RC[-
Click to expand...
2
],3)&""-""&LEFT(RC[-1],2)&""-""&RIGHT(RC[-1],4)))

Can anyone offer a suggestion for me?
Click to expand...

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).
Click to expand...
 
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.
Click to expand...

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"))
 
Back
Top