Data needs to be the same length in a field by pre-filling with a

[Jen Scott]

I need to take a field where the data is different lengths and export it out
as all the same length by putting zeros in front of the data.

ie. 1234, 123, 98766875 need to look like this when exported:

000001234
000000123
098766875

 

[Ken Sheridan]

Export it as text by means of the Format function, so in a query from which
the data is to be exported you'd put something like the following in the
'field' row of a blank column in the design grid:

MyFieldFormatted:Format([MyField],"000000000")

Ken Sheridan
Stafford, England

 

[Jen Scott]

Thanks for your help ---

One thing I forgot to mention that might make it more difficult -- if I also
need to append 2 zeros at the end of every row -- how would I do that?




--
Jen Scott

Export it as text by means of the Format function, so in a query from which
the data is to be exported you'd put something like the following in the
'field' row of a blank column in the design grid:

MyFieldFormatted:Format([MyField],"000000000")

Ken Sheridan
Stafford, England

I need to take a field where the data is different lengths and export it out
as all the same length by putting zeros in front of the data.

ie. 1234, 123, 98766875 need to look like this when exported:

000001234
000000123
098766875

 

[Ken Sheridan]

Just multiply the value by 100:

MyFieldFormatted:Format([MyField]*100,"00000000000")

Note that you must multiply it by 100 *before* formatting. If you multiply
the formatted value by 100 you'll end up with a number again, with the two
trailing zeros but not the leading zeros.

Ken Sheridan
Stafford, England

Thanks for your help ---

One thing I forgot to mention that might make it more difficult -- if I also
need to append 2 zeros at the end of every row -- how would I do that?




--
Jen Scott

Export it as text by means of the Format function, so in a query from which
the data is to be exported you'd put something like the following in the
'field' row of a blank column in the design grid:

MyFieldFormatted:Format([MyField],"000000000")

Ken Sheridan
Stafford, England

I need to take a field where the data is different lengths and export it out
as all the same length by putting zeros in front of the data.

ie. 1234, 123, 98766875 need to look like this when exported:

000001234
000000123
098766875

 

[Jen Scott]

OK -- that works for numbers, but if I needed to do it for a text field --
how would i do it?

I need to do the same thing, but fill with the letter "I" in front and two
zeros at the end. The originating field is a number field, but I could
change it to text if I needed to.

ie. 1234 and 123 need to look like this when exported:

IIIII123400
IIIIII12300

Thanks for all of your help!!


--
Jen Scott

Just multiply the value by 100:

MyFieldFormatted:Format([MyField]*100,"00000000000")

Note that you must multiply it by 100 *before* formatting. If you multiply
the formatted value by 100 you'll end up with a number again, with the two
trailing zeros but not the leading zeros.

Ken Sheridan
Stafford, England

Thanks for your help ---

One thing I forgot to mention that might make it more difficult -- if I also
need to append 2 zeros at the end of every row -- how would I do that?

00000123400
00000012300
09876687500

--
Jen Scott

Export it as text by means of the Format function, so in a query from which
the data is to be exported you'd put something like the following in the
'field' row of a blank column in the design grid:

MyFieldFormatted:Format([MyField],"000000000")

Ken Sheridan
Stafford, England

:

I need to take a field where the data is different lengths and export it out
as all the same length by putting zeros in front of the data.

ie. 1234, 123, 98766875 need to look like this when exported:

000001234
000000123
098766875

 

[Hans Up]

OK -- that works for numbers, but if I needed to do it for a text field --
how would i do it?

I need to do the same thing, but fill with the letter "I" in front and two
zeros at the end. The originating field is a number field, but I could
change it to text if I needed to.

ie. 1234 and 123 need to look like this when exported:

IIIII123400
IIIIII12300

If MyField will always have at least one character, you might try this:

MyFieldFormatted:Right$((String(9, "I") & [MyField] & "00"), 11)

But if MyField can ever be Null and/or an empty string (""), what do you
want MyFieldFormatted to display?

Hans