Check for lost leading zeros

[Bonnie]

Hi there all! Using A02 on XP. I have some data (including
social security numbers) that is imported from a comma
delimited file (.csv). Once in a while there is a leading
zero that gets dropped in the .csv file and so once it
lands in access, a few SSN's have only 8 (or even 7)
digits.

What is the most efficient (but not too difficult) method
to run a query or somehow check to see if any SSN's need
to be edited? (I would love to then just insert the 1 or 2
leading zeros that have disappeared.) I can parse out all
9 digits and then put 'is null' in the criteria line of a
query to see if one is missing but it's got to able to be
done easier than that.

Thanks in advance for any help or advice on this!!! Luv U
guys!!!

 

[Bruce M. Thompson]

Hi there all! Using A02 on XP. I have some data (including

social security numbers) that is imported from a comma
delimited file (.csv). Once in a while there is a leading
zero that gets dropped in the .csv file and so once it
lands in access, a few SSN's have only 8 (or even 7)
digits.

What is the most efficient (but not too difficult) method
to run a query or somehow check to see if any SSN's need
to be edited? (I would love to then just insert the 1 or 2
leading zeros that have disappeared.) I can parse out all
9 digits and then put 'is null' in the criteria line of a
query to see if one is missing but it's got to able to be
done easier than that.

First of all, change the SSN field's data type from Number to Text (you can't
store leading zeros in a number field), then run an update query against the SSN
field to update the SSN's value to the following (this, of course, assumes the
standard US social security numbers):

Format([SSN], "000000000")

If the SSN field's name differs from that in my example, change it to match that
in your project.

 

[John Vinson]

What is the most efficient (but not too difficult) method
to run a query or somehow check to see if any SSN's need
to be edited?

Create a Query with a calculated field:

SSNSize: Len([SSN])

and use a criterion of <9 on it.

You can even make it an Update query and update SSN to

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

to put as many leading zeros as needed on every incorrect value.

 

[Bonnie]

Exactamundo, dude!!! John, thank you SOOOO much. It is
just what I needed (but never used Len before). I'll doc
it in my binder for future ref.

Again, thanks and I truly appreciate the time you dedicate
to the newsgroups.

-----Original Message-----

What is the most efficient (but not too difficult) method
to run a query or somehow check to see if any SSN's need
to be edited?

Create a Query with a calculated field:

SSNSize: Len([SSN])

and use a criterion of <9 on it.

You can even make it an Update query and update SSN to

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

to put as many leading zeros as needed on every incorrect value.


.

 

[Bonnie]

Thanks, Bruce, for the quick reply. My access field is
indeed text in the import specs for those very reasons. (I
have to use SSN ALOT and receive it in many formats, sizes
and shapes.) John gave me the perfect way to check for it
and I like your method to update so I'll be playing with
them both here shortly.

Thanks again and I appreciate your time. It really helps.

-----Original Message-----

Hi there all! Using A02 on XP. I have some data (including
social security numbers) that is imported from a comma
delimited file (.csv). Once in a while there is a leading
zero that gets dropped in the .csv file and so once it
lands in access, a few SSN's have only 8 (or even 7)
digits.

What is the most efficient (but not too difficult) method
to run a query or somehow check to see if any SSN's need
to be edited? (I would love to then just insert the 1 or 2
leading zeros that have disappeared.) I can parse out all
9 digits and then put 'is null' in the criteria line of a
query to see if one is missing but it's got to able to be
done easier than that.

First of all, change the SSN field's data type from Number to Text (you can't
store leading zeros in a number field), then run an update query against the SSN
field to update the SSN's value to the following (this, of course, assumes the
standard US social security numbers):

Format([SSN], "000000000")

If the SSN field's name differs from that in my example, change it to match that
in your project.

--
Bruce M. Thompson, Microsoft Access MVP
(e-mail address removed) (See the Access FAQ at http://www.mvps.org/access)within the newsgroups so that all might benefit.<<


.