AS400 Leading Zero's

[Warren Phillips]

Hi:

I am downloading data from our AS400 that includes the SSN. For some
reason, Client Access is dropping any leading zero's in the SSN......this
means that my exported .txt file does not contain ANY leading zero's.

So far I found examples of employees with one or two leading zero's in their
SS numbers.

I have played around with the various methods of downloading the data from
Client Access (AS400)...but cannot find a combination that will bring down a
DELIMITED file WITH the leading zero's.

I am bringing my .txt file into Access 2003. Is there a way to force Access
to look at the this field.....apply some logic like (must be 9 digits)...and
then add one or more zeros to make the total number of characters be equal to
"9"

Thanks

 

[Douglas J. Steele]

Access is obviously treating the SSNs as numbers, not text. (SSNs should
always be text, to prevent problems with leading zeroes, and since you're
not going to be doing arithmetic using them).

Add a new Text field to your table and update it from the existing numeric
field using the Format function: Format(NumericSSN, "000000000")

 

[Warren Phillips]

Actually, the .txt file is coming in Access as TEXT. The zeros are getting
stripped during the download process from our AS400...

Would this change your advice...

Also.....can you add a little more on using this part of your posting
"Format function: Format(NumericSSN, "000000000")"

Where do I put this code...In the TABLE..Are you saying do an UPDATE QUERY??

 

[Douglas J. Steele]

Yes, I was saying to use an Update query to populate the new text SSN:

UPDATE MyTable SET TextSSN = Format(NumericSSN, "000000000")

Another option would be to leave the data the way it is, and create a query
that uses the Format function to present the SSN, and use the query wherever
you would otherwise have used the table:

SELECT Field1, Field2, Format(NumericSSN, "000000000") AS TextSSN
FROM MyTable