Please help to zero filled on file

[Duyen]

I'm being having trouble setting up an update query that would help me update
the amount field on my report to zero filled the space.
For example:
Amount Want to be
150 00000000015000

The total characters of this field is 14. I've tried to format the table to
zero filled it, which works on the table. However, when I export it out to
text, which is my main goal is to export it out to text, it won't show the
00000000 in front. Can someone please help me with this. I have about 7
fields like this within one file that I need to update. Please help!!!

Thanks,

 

[KARL DEWEY]

What kind of field is it you are trying to update?
Why update at all, just format it on display or export.

 

[KARL DEWEY]

Could you not use what Marshall Barton posted on 12 Sep?

 

[Duyen]

I have tried that advise. Which I reformat the column to zero filled it by
change the format to Number and under format, I typed in 0000000000000 (14
zeros), and yes, it does show up the way I wanted it to be on the table in
Access. But, the end result is that I want it to export it out to text file
with these zeros and it did not exported out these zeros.

Is there another way?

 

[Duyen]

It's originally a text field with number, which I reformated it to number
field with 0000000000 format. But export does not work because it does not
include the "0000..." in front that I want it to be. Please help!!!

 

[KARL DEWEY]

Formating is only for display and not for storage, the field in the table is
not storing what you ask it to display.
Format not the table but the export. How are you exporting it?

 

[John Spencer]

Export via a query and use the format function to do so. Use a calculated
field that looks something like the following. That should turn 150 into a
string that looks like 0000015000.

Field: FormattedYourNumber: Format([YourNumber]*100,"0000000000")

If you need to keep the field names the same, you can probably do that by
specifying the table and field name in the format function.

Field: YourNumber: Format([YourTableName].[YourNumber]*100,"0000000000")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Duyen]

I go to file and select export to text file with Fixed Width selection.

 

[Duyen]

Thank you so much. This works great. Thank you for the advise.

Export via a query and use the format function to do so. Use a calculated
field that looks something like the following. That should turn 150 into a
string that looks like 0000015000.

Field: FormattedYourNumber: Format([YourNumber]*100,"0000000000")

If you need to keep the field names the same, you can probably do that by
specifying the table and field name in the format function.

Field: YourNumber: Format([YourTableName].[YourNumber]*100,"0000000000")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

It's originally a text field with number, which I reformated it to number
field with 0000000000 format. But export does not work because it does not
include the "0000..." in front that I want it to be. Please help!!!