Formatting the SSN

[steve12173]

I have the SSN formatted to be stored without the dashes. Someone came in
and appended a few thousand records that have the dashes in the SSN. Is
there any way of removing those dashes without going through every record?

 

[fredg]

I have the SSN formatted to be stored without the dashes. Someone came in
and appended a few thousand records that have the dashes in the SSN. Is
there any way of removing those dashes without going through every record?

You can run an Update query to remove the dashes in those records that
contain them.

Update YourTable Set YourTable.[SSN] = Replace([SSN],"-","");

 

[BruceM]

You could run an update query. Back up the database first. The query SQL
will be something like this:

UPDATE YourTable SET YourTable.SSN = Replace([SSN],"-","")

You could also construct the query in design view, adding only the SSN
field. From the Query menu, select Update Query. In the Update To row:
Replace([SSN],"-","")

In either case, select Run from the Query menu or by way of the toolbar.

As an aside, your description does not speak well of database security in a
database containing SSNs. Perhaps this is because of an abbreviated
description of the situation, but if "somebody" has access to all of those
SSNs, who else does? Be aware of the liability implications of making such
data available to other than people authorized to see it.

 

[Arvin Meyer MVP]

Let me reiterate Bruce's concern over SSN's. New and current laws make you
responsible for protecting personal information, especially SSN's, CC
numbers, and health and financial information. Access can be made to conform
to the security required, although to be really secure, you should be using
a SQL-Server back-end. No one, under any condition, should be able to just
append or copy that private information in the wholesale manner described.

 

[Proposal Doctor]

Hello Steve,

How did your users append the records to the existing file? I tried doing
it from Excel, but Access refused to do so. It would append records with
nine characters but it would not accept 11 (i.e., nine digits and two
dashes).

Are the bad records complete SSNs or truncated at 9 characters?

David

 

[Douglas J. Steele]

How have you declared the SSN field in Access? Did you declare it as Text 9?

 

[Proposal Doctor]

Yes, I did. Steve stated that he had set his SSN field to store the data
without dashes. So I don't understand how his users could append data with
dashes and still have a legal SSN.

David

 

[Douglas J. Steele]

He may have had an input mask defined.

 

[steve12173]

Thanks for the help on running the Update Query, worked perfectly. In using
the Update Query you provided "Replace([SSN],"-","")", it did drop off the
leading zero. Which, I just did another Update Query "Right("000000000" &
[SSN], 9)" to add the preceding zeroes back.


As to your side note. I can assure you that our computer systems and the
database our completely secure. This was a manager who decided to copy and
paste a load of info from Excel.

Thanks again!

You could run an update query. Back up the database first. The query SQL
will be something like this:

UPDATE YourTable SET YourTable.SSN = Replace([SSN],"-","")

You could also construct the query in design view, adding only the SSN
field. From the Query menu, select Update Query. In the Update To row:
Replace([SSN],"-","")

In either case, select Run from the Query menu or by way of the toolbar.

As an aside, your description does not speak well of database security in a
database containing SSNs. Perhaps this is because of an abbreviated
description of the situation, but if "somebody" has access to all of those
SSNs, who else does? Be aware of the liability implications of making such
data available to other than people authorized to see it.

I have the SSN formatted to be stored without the dashes. Someone came in
and appended a few thousand records that have the dashes in the SSN. Is
there any way of removing those dashes without going through every record?

 

[BruceM]

I'm not sure what happened there. SSN seems to be behaving as a number
field, when it should be a text field. However, if it was a number field I
don't think it would have stored the dashes, and in any case the Replace
function returns a string, as does Right. It probably would have worked to
use Format with Replace:
=Format(Replace([SSN],"-",""),"000000000")

Also, you could have formatted SSN as "000\-00\-0000" when you need to
display it. Your solution is OK too; just pointing out some options that
may come in handy at some time.

Thanks for the help on running the Update Query, worked perfectly. In
using
the Update Query you provided "Replace([SSN],"-","")", it did drop off the
leading zero. Which, I just did another Update Query "Right("000000000" &
[SSN], 9)" to add the preceding zeroes back.


As to your side note. I can assure you that our computer systems and the
database our completely secure. This was a manager who decided to copy
and
paste a load of info from Excel.

Thanks again!

You could run an update query. Back up the database first. The query
SQL
will be something like this:

UPDATE YourTable SET YourTable.SSN = Replace([SSN],"-","")

You could also construct the query in design view, adding only the SSN
field. From the Query menu, select Update Query. In the Update To row:
Replace([SSN],"-","")

In either case, select Run from the Query menu or by way of the toolbar.

As an aside, your description does not speak well of database security in
a
database containing SSNs. Perhaps this is because of an abbreviated
description of the situation, but if "somebody" has access to all of
those
SSNs, who else does? Be aware of the liability implications of making
such
data available to other than people authorized to see it.

I have the SSN formatted to be stored without the dashes. Someone came
in
and appended a few thousand records that have the dashes in the SSN.
Is
there any way of removing those dashes without going through every
record?