Convert Intiger to 9 Character Text with Leading Zeros

[Guest]

I have a number field that contains numbers from 555555 to 199999999. I need
to convert the data to a 9 character text field with leading zeros. Numbers
with 6 digits require 3 leading zeros, 7 requires 2, 8 requires 1, and 9
digit requires no conversion (all existing 9 digit numbers begin with "1").
I used a make table query to add leading zeros to a new field as follows -
NewCustomerNum: "000" & [CustomerNum]. However, that adds 3 zeros to EVERY
Customer Number. I tried to limit the query by using criteria - CustomerNum
Between 654321 and 999999. That works for 6 digit customer numbers but when
I tried to add additional statements to add only 2 zeros for ...Between
1000000 and 9999999, 1 zero for et al, Access rejected the syntax and I can't
figure it out. I could write 3 separate queries but I'd like to do it with
one.
I also tried an update query where I pre-set the NewCustomerNum field to 9
digits. However when the query added the leading zeros, it added the zeros
to the three most significant digits and cut off the 3 least significint
digits of the 7, 8, and 9 digit numbers.

Any suggestions?

 

[Ken Snell [MVP]]

Create a text field in the table. Use this query to copy the data to that
text field:

UPDATE YourTableName
SET NewTextFieldName = Format(CurrentNumberField, "000000000");

 

[fredg]

I have a number field that contains numbers from 555555 to 199999999. I need
to convert the data to a 9 character text field with leading zeros. Numbers
with 6 digits require 3 leading zeros, 7 requires 2, 8 requires 1, and 9
digit requires no conversion (all existing 9 digit numbers begin with "1").
I used a make table query to add leading zeros to a new field as follows -
NewCustomerNum: "000" & [CustomerNum]. However, that adds 3 zeros to EVERY
Customer Number. I tried to limit the query by using criteria - CustomerNum
Between 654321 and 999999. That works for 6 digit customer numbers but when
I tried to add additional statements to add only 2 zeros for ...Between
1000000 and 9999999, 1 zero for et al, Access rejected the syntax and I can't
figure it out. I could write 3 separate queries but I'd like to do it with
one.
I also tried an update query where I pre-set the NewCustomerNum field to 9
digits. However when the query added the leading zeros, it added the zeros
to the three most significant digits and cut off the 3 least significint
digits of the 7, 8, and 9 digit numbers.

Any suggestions?

[FieldName] must be a Text datatype field, NOT a Number datatype.
Number datatypes do NOT display leading Zero's.

To convert your existing field you can add a new Text datatype field
to the table and fill it with formatted numbers from the Number field.

Update YourTable Set YourTable.[FieldName2] =
Format([FieldName1],"000000000")

 

[Guest]

Thanks - it worked! I tried a similar version of your solution that didn't
work. Now that I see it, I see what I did wrong. Thanks again.

Create a text field in the table. Use this query to copy the data to that
text field:

UPDATE YourTableName
SET NewTextFieldName = Format(CurrentNumberField, "000000000");

--

Ken Snell
<MS ACCESS MVP>

I have a number field that contains numbers from 555555 to 199999999. I
need
to convert the data to a 9 character text field with leading zeros.
Numbers
with 6 digits require 3 leading zeros, 7 requires 2, 8 requires 1, and 9
digit requires no conversion (all existing 9 digit numbers begin with
"1").
I used a make table query to add leading zeros to a new field as follows -
NewCustomerNum: "000" & [CustomerNum]. However, that adds 3 zeros to
EVERY
Customer Number. I tried to limit the query by using criteria -
CustomerNum
Between 654321 and 999999. That works for 6 digit customer numbers but
when
I tried to add additional statements to add only 2 zeros for ...Between
1000000 and 9999999, 1 zero for et al, Access rejected the syntax and I
can't
figure it out. I could write 3 separate queries but I'd like to do it
with
one.
I also tried an update query where I pre-set the NewCustomerNum field to 9
digits. However when the query added the leading zeros, it added the
zeros
to the three most significant digits and cut off the 3 least significint
digits of the 7, 8, and 9 digit numbers.

Any suggestions?

 

[Guest]

Thanks, this solutions works great too!

I have a number field that contains numbers from 555555 to 199999999. I need
to convert the data to a 9 character text field with leading zeros. Numbers
with 6 digits require 3 leading zeros, 7 requires 2, 8 requires 1, and 9
digit requires no conversion (all existing 9 digit numbers begin with "1").
I used a make table query to add leading zeros to a new field as follows -
NewCustomerNum: "000" & [CustomerNum]. However, that adds 3 zeros to EVERY
Customer Number. I tried to limit the query by using criteria - CustomerNum
Between 654321 and 999999. That works for 6 digit customer numbers but when
I tried to add additional statements to add only 2 zeros for ...Between
1000000 and 9999999, 1 zero for et al, Access rejected the syntax and I can't
figure it out. I could write 3 separate queries but I'd like to do it with
one.
I also tried an update query where I pre-set the NewCustomerNum field to 9
digits. However when the query added the leading zeros, it added the zeros
to the three most significant digits and cut off the 3 least significint
digits of the 7, 8, and 9 digit numbers.

Any suggestions?

[FieldName] must be a Text datatype field, NOT a Number datatype.
Number datatypes do NOT display leading Zero's.

To convert your existing field you can add a new Text datatype field
to the table and fill it with formatted numbers from the Number field.

Update YourTable Set YourTable.[FieldName2] =
Format([FieldName1],"000000000")