Formatting the SSN

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?

On Fri, 26 Jun 2009 08:11:01 -0700, steve12173 wrote:

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],"-","");


--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

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.

"steve12173" wrote in message
...
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?

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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"BruceM" bamoob_at_yawhodotcalm.not wrote in message
...

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.

"steve12173" wrote in message
...
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?

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

"steve12173" wrote:

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?

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Proposal Doctor" wrote in
message ...
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

"steve12173" wrote:

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?

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" wrote:

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Proposal Doctor" wrote in
message ...
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

"steve12173" wrote:

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?

He may have had an input mask defined.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Proposal Doctor" wrote in
message ...
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" wrote:

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Proposal Doctor" wrote in
message ...
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

"steve12173" wrote:

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?

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!

"BruceM" wrote:

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.

"steve12173" wrote in message
...
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?

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.

"steve12173" wrote in message
...
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!

"BruceM" wrote:

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.

"steve12173" wrote in message
...
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?