Front load a string with zeros

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I copied a number field from table to a text field in another table. The
text field is a length of 7. It contains charecters that I want to front
load with zeros. For example (9 ), I want to be (0000009), a (1234 )
I want it be (0001234). Is there a way in a query to accomplish this? If
you need to write code to open the file and read all the records, identifying
the length and adding 0s, then I really need help.
 
If you format either the original number field or the resulting text field,
it front loads the zeros. However when you export the data, it remembers it
was a number and exports only the significant characters. The text file
looks like 0001234, the output text file looks like "1234 ".

KARL DEWEY said:
Try this --
Format([YourField],"0000000")

NewHeartMan said:
I copied a number field from table to a text field in another table. The
text field is a length of 7. It contains charecters that I want to front
load with zeros. For example (9 ), I want to be (0000009), a (1234 )
I want it be (0001234). Is there a way in a query to accomplish this? If
you need to write code to open the file and read all the records, identifying
the length and adding 0s, then I really need help.
Click to expand...
Click to expand...
 
How and where are you exporting to?

If to an Excel file then try --

Format([YourField],"'0000000")

That has a single quote before the zeros.

NewHeartMan said:
If you format either the original number field or the resulting text field,
it front loads the zeros. However when you export the data, it remembers it
was a number and exports only the significant characters. The text file
looks like 0001234, the output text file looks like "1234 ".

KARL DEWEY said:
Try this --
Format([YourField],"0000000")

NewHeartMan said:
I copied a number field from table to a text field in another table. The
text field is a length of 7. It contains charecters that I want to front
load with zeros. For example (9 ), I want to be (0000009), a (1234 )
I want it be (0001234). Is there a way in a query to accomplish this? If
you need to write code to open the file and read all the records, identifying
the length and adding 0s, then I really need help.
Click to expand...
Click to expand...
Click to expand...
 
If you want to directly modify the data on a permanent basis:
First of all, it has to be a STRING, not a number. You may need to
change the field type, and/or you may have to convert the number to a
string. Cstr() will do that.

You need to know how many characters you have already. Len() will do that.
You need to create a "0" for each of the extra characters. String will
do that.

in English:
Build a string of "0"s, with as many characters as 7 minus the number of
characters in my field, and append my field to the end of it.

Function

String("0",7-len([field]))&[field]







If you format either the original number field or the resulting text field,
it front loads the zeros. However when you export the data, it remembers it
was a number and exports only the significant characters. The text file
looks like 0001234, the output text file looks like "1234 ".

:

Try this --
Format([YourField],"0000000")

:

I copied a number field from table to a text field in another table. The
text field is a length of 7. It contains charecters that I want to front
load with zeros. For example (9 ), I want to be (0000009), a (1234 )
I want it be (0001234). Is there a way in a query to accomplish this? If
you need to write code to open the file and read all the records, identifying
the length and adding 0s, then I really need help.
Click to expand...
Click to expand...
Click to expand...
 
I am exporting it to a text file. I don't know how many characters I have in
the string. I just know the length is 7. It looks as though I have to open
the table, read the records, check the length of the field and then front
load it with 0s.

KARL DEWEY said:
How and where are you exporting to?

If to an Excel file then try --

Format([YourField],"'0000000")

That has a single quote before the zeros.

NewHeartMan said:
If you format either the original number field or the resulting text field,
it front loads the zeros. However when you export the data, it remembers it
was a number and exports only the significant characters. The text file
looks like 0001234, the output text file looks like "1234 ".

KARL DEWEY said:
Try this --
Format([YourField],"0000000")

:

I copied a number field from table to a text field in another table. The
text field is a length of 7. It contains charecters that I want to front
load with zeros. For example (9 ), I want to be (0000009), a (1234 )
I want it be (0001234). Is there a way in a query to accomplish this? If
you need to write code to open the file and read all the records, identifying
the length and adding 0s, then I really need help.
Click to expand...
Click to expand...
Click to expand...
 
NewHeartMan said:
I am exporting it to a text file. I don't know how many characters I
have in the string. I just know the length is 7. It looks as though
I have to open the table, read the records, check the length of the
field and then front load it with 0s.
Click to expand...

No, Format([YourField],"'0000000") will always produce a string 7 characters
long with as many leading zeros as it takes to achieve that.


Examples:

Format(12345,"'0000000") = "0123456"
Format(1,"'0000000") = "0000001"
 
Back
Top