Insert Varying Leading Zeros

C

Commish

I have a column of data that varies from 3 to 10 characters. I need to
add the variable number of leading zeros to be able to join it to a
column in a different table - where that column if data has the
leading zeros and is always 10 characters in length. Also, note that
the column of 3-10 characters is a mix of letters and numbers.

It'll be easy enough to do the following: IF(len([Field1])
=3,"0000000"&[Field1])... and then continue on populating the if/then
with 6 zeros, than 5 zeroes, and so on.

But, since I have some time, i was wondering if anyone else has done
this and has a procedure/methodology that is reusable in this scenario.
 
V

vanderghast

RIGHT(STRING(10, "0") & fieldName , 10 )

should do. All cap words are keyword to be typed as they appear. fieldName
has to be replaced by your real fiel name.





Vanderghast, Access MVP
 
C

Commish

    RIGHT(STRING(10, "0") & fieldName , 10 )

should do.  All cap words are keyword to be typed as they appear. fieldName
has to be replaced by your real fiel name.

Vanderghast, Access MVP


I have a column of data that varies from 3 to 10 characters. I need to
add the variable number of leading zeros to be able to join it to a
column in a different table - where that column if data has the
leading zeros and is always 10 characters in length. Also, note that
the column of 3-10 characters is a mix of letters and numbers.
It'll be easy enough to do the following: IF(len([Field1])
=3,"0000000"&[Field1])... and then continue on populating the if/then
with 6 zeros, than 5 zeroes, and so on.
But, since I have some time, i was wondering if anyone else has done
this and has a procedure/methodology that is reusable in this scenario.

Wow - that was even easier than I expected - I've already used it
twice. Thanks.
 

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