Add trailing zeros

[JOM]

I Have Request number in my database, whose input mask is !0000000000;;_ and
datatype is text....
I copeied the information from the database and pasted the information in a
spreadsheet. I transfered the same information to a different database but
the thing is that the spreadsheet omitted the trailing zeros

My request numbers look like this

123456789 instead of 0123456789
23456789 instead of 0023456789

So there are some that will need 2 trailing zeros while others will require
only 1... How do I update the request numbers? I have manually changed some
to how they are supposed look like but there are more 1000 that need to be
updated....

 

[John Vinson]

I Have Request number in my database, whose input mask is !0000000000;;_ and
datatype is text....
I copeied the information from the database and pasted the information in a
spreadsheet. I transfered the same information to a different database but
the thing is that the spreadsheet omitted the trailing zeros

My request numbers look like this

123456789 instead of 0123456789
23456789 instead of 0023456789

So there are some that will need 2 trailing zeros while others will require
only 1... How do I update the request numbers? I have manually changed some
to how they are supposed look like but there are more 1000 that need to be
updated....

Leading zeros, not trailing zeros, right?

When you put the data into Excel and then back into Access, it's being
interpreted as a Number datatype (Access makes you specify datatypes;
Excel doesn't LET you do so).

You can reinsert the leading zeros by running an Update query updating
the field to

Right("0000000000" & [fieldname], 10)

Note that the field MUST be of Text datatype, not any sort of number;
the *numbers* 2, 02, and 00000000002 are all exactly the same number.

John W. Vinson[MVP]

 

[Ofer]

Back up your data first
Use update query with format, assuming that the field type is text, it wont
work with numeric field

Update TableName Set FieldName = Format(FieldName,"0000000000")

 

[JOM]

Thanks alot that worked!
You are right, I ment leading zeros!

I Have Request number in my database, whose input mask is !0000000000;;_ and
datatype is text....
I copeied the information from the database and pasted the information in a
spreadsheet. I transfered the same information to a different database but
the thing is that the spreadsheet omitted the trailing zeros

My request numbers look like this

123456789 instead of 0123456789
23456789 instead of 0023456789

So there are some that will need 2 trailing zeros while others will require
only 1... How do I update the request numbers? I have manually changed some
to how they are supposed look like but there are more 1000 that need to be
updated....

Leading zeros, not trailing zeros, right?

When you put the data into Excel and then back into Access, it's being
interpreted as a Number datatype (Access makes you specify datatypes;
Excel doesn't LET you do so).

You can reinsert the leading zeros by running an Update query updating
the field to

Right("0000000000" & [fieldname], 10)

Note that the field MUST be of Text datatype, not any sort of number;
the *numbers* 2, 02, and 00000000002 are all exactly the same number.

John W. Vinson[MVP]

 

[JOM]

Thanks alot, that worked perfect

Back up your data first
Use update query with format, assuming that the field type is text, it wont
work with numeric field

Update TableName Set FieldName = Format(FieldName,"0000000000")

 

[mnature]

If, for any reason, you would prefer the field to stay as a number, you can
set the format using zeros (such as 000000 for a six-digit number), and when
you input a number, it will use leading zeros for any number with fewer
digits than the number of zeros you have set for the formatting.