Insert Varying Leading Zeros

[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.

 

[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

 

[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.