AS400 Leading Zero's

  • Thread starter Warren Phillips
  • Start date
W

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
 
D

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")
 
W

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??
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top