SSNs

D

Dobens

The database that I draw info from drops the leading zeros in the SSN field.
The user of the DB needs the leading zeros in the field and not just a space.
Any suggestions as to how to keep those zeros in?
 
R

Rick Brandt

Dobens said:
The database that I draw info from drops the leading zeros in the SSN
field. The user of the DB needs the leading zeros in the field and
not just a space. Any suggestions as to how to keep those zeros in?
Click to expand...

SSNs belong in a Text field, not a Number field. While they are described
as "numbers" they don't need to be in an actual number field unless you
intend to perform numeric calculations on them.

You will likely get several responses warning you about storing SSNs in an
MDB file so I will just pre-emptively add my own warning that this had
better be a well-protected file if you want to avoid potential legal
problems.
 
B

BruceM

Formatting the field as text rather than number should do the trick. Also,
I'm not ordinarily a fan of input masks, put SSN is one place where an input
mask in the control (text box, I would assume in your case) could actually be
helpful.
 
A

Arvin Meyer

You will likely get several responses warning you about storing SSNs in an
MDB file so I will just pre-emptively add my own warning that this had
better be a well-protected file if you want to avoid potential legal
problems.
Click to expand...

Given the amount of identity theft, the reassessment of legal liability, and
the possibility of privacy invasion, that's excellent advice. Access is not
really secure enough by itself to satisfy the requirements of a truly secure
environment. Physical locks on server locations, complex password protected
workstations, and security checks on employees, are necessary to ensure
adequate security. If one doesn't absolutely need to store an SSN, one
should avoid it.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access
 
D

Dobens

The data comes over from a Unix platform and doesn't allow me to change the
fields. Would you recommend that I do a make table of the data and then
change the field in the new table?
 
D

Dobens

I tried that and it didn't work. For those fields where the data came over
with fewer that nine digits, the input mask did nothing to it. For the other
data, the result was as expected "123-45-6789".
 
D

Dobens

Thanks for all the warnings.

Arvin Meyer said:
Given the amount of identity theft, the reassessment of legal liability, and
the possibility of privacy invasion, that's excellent advice. Access is not
really secure enough by itself to satisfy the requirements of a truly secure
environment. Physical locks on server locations, complex password protected
workstations, and security checks on employees, are necessary to ensure
adequate security. If one doesn't absolutely need to store an SSN, one
should avoid it.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access
Click to expand...
 
B

BruceM

I am not familiar with migrating data from other applications, and even less
so from other platforms. In general I would have to say that you need any
imported data in a format you can use. That may mean you need to use a
make-table query, or that you need to export from the other platform to a
text file or some other format that both platforms can use, then import it
into an Access table, but as I said, I cannot address the details of doing so.
 
D

Dobens

Lynn:

While it is in the Unix database, the leading zeros are discarded. When it
comes over to Windows, it is a text field but without those zeros.
 
J

John Vinson

Lynn:

While it is in the Unix database, the leading zeros are discarded. When it
comes over to Windows, it is a text field but without those zeros.
Click to expand...

If you're losing the zeroes even before Access sees the data, you may
just need to run an Update query. Assuming that your field is nine
characters long, you can update it to

Right("000000000" & [SSN], 9)

to insert as many zeros as needed to bring it up to 9 bytes.

John W. Vinson[MVP]
 
D

Dobens

JOhn:

That did it. Thanks very much!

Chas

John Vinson said:
Lynn:

While it is in the Unix database, the leading zeros are discarded. When it
comes over to Windows, it is a text field but without those zeros.
Click to expand...

If you're losing the zeroes even before Access sees the data, you may
just need to run an Update query. Assuming that your field is nine
characters long, you can update it to

Right("000000000" & [SSN], 9)

to insert as many zeros as needed to bring it up to 9 bytes.

John W. Vinson[MVP]
Click to expand...
 
B

BruceM

Make a query including all of the fields from the table, and add a calculated
field that I will call CalcSSN (SSN is your Social Security Number field):
CalcSSN: IIf(Len([SSN])=7,"00" & [SSN],IIf(Len([SSN])=8,"0" & [SSN],[SSN]))
Check the results, then run it as a make-table query (see Help for more on
this). Delete the old field, rename the new one, and apply the input mask.
 
B

BruceM

Didn't notice this reply when I posted mine. I was in another branch of the
thread. This is much simpler than my solution using the LEN function.

Dobens said:
JOhn:

That did it. Thanks very much!

Chas

John Vinson said:
Lynn:

While it is in the Unix database, the leading zeros are discarded. When it
comes over to Windows, it is a text field but without those zeros.
Click to expand...

If you're losing the zeroes even before Access sees the data, you may
just need to run an Update query. Assuming that your field is nine
characters long, you can update it to

Right("000000000" & [SSN], 9)

to insert as many zeros as needed to bring it up to 9 bytes.

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
Top