Format SSNs

[Guest]

In my Table (and my Input Form) I have the SSNs formatted so they'll show the
dashes: 123-45-6789. I am running a Query to export the data into an Excel
spreadsheet, and I need to lose the dashes (123456789). I've tried changing
the properties of the SSN field in the Query, but it doesn't work. Please
help.

 

[fredg]

In my Table (and my Input Form) I have the SSNs formatted so they'll show the
dashes: 123-45-6789. I am running a Query to export the data into an Excel
spreadsheet, and I need to lose the dashes (123456789). I've tried changing
the properties of the SSN field in the Query, but it doesn't work. Please
help.

If the SSN is in fact stored with the hyphens, and your Access version
supports the replace function....
In the query:
NewSSN:Replace([SSN],"-","")

If it doesn't support the Replace function, you can do something like
this:
NewSSN:Left([SSN],3) & Mid([SSN],5,2) & Right([SSN],4)

Send this NewSSN to the spreadsheet.

 

[Guest]

Try this in the query

Select Replace([Fieldname],"-","") as NewSSN From TableName

 

[Guest]

Thanks to both of you. This is exactly what I needed!

 

[Guest]

Your welcome, Good luck

 

[Guest]

Please don't store SSNs in an Access database! Your company could leave
itself open to lawsuits. You should store artificial employee id's not the
actual SSN.

 

[Vincent Johns]

Please don't store SSNs in an Access database! Your company could leave
itself open to lawsuits. You should store artificial employee id's not the
actual SSN.

But this would also be true for storing names, addresses, bank account
numbers, ..., just about anything that can be considered personal. In
Europe, an employee's office telephone number is considered sensitive
information, and I have heard that publishing a telephone directory only
for company internal use that includes names and phone numbers is
illegal (new law, a few months ago).

The fact that an SSAN is stored in a database is not (per se) cause for
lawsuits, if you have the right to know the SSAN, and you didn't
improperly publish it. (Watch out! Don't consider this legal advice --
I'm not an attorney!) Any database with personal information should be
protected, and that applies to all the personal information, not only SSANs.

It probably is a good idea not to print out or display sensitive
information like SSANs unless you're prepared to protect the documents
and display devices just as carefully as you do the database.

Oh yes, and perhaps this is obvious, but if you DON'T have a right to
know the SSANs, I suggest erasing that field from your Tables, and maybe
editing the backup copies of your database so that they don't contain
anything illegal either.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.