How do I keep leading zeros and remove the decimal point when expo

  • Thread starter Thread starter MarieT
  • Start date Start date
M

MarieT

Hello,

How can I export an amount field in an Access table to a fixed length text
file without losing the leading zeros and removing the decimal point (having
an implied decimal point)? The Export Spec file does not allow me to specify
the type of the field I am exporting. Here are the details:

I imported an amount field into a new table using a spec file where I
defined the amount field as a double. The fixed length input file has the
amount field in the following format: 9877.15 (with no leading zeros, and the
length of the field being 15).
I have to export the info in my table to another fixed length text file with
the information formatted differently. Specifically, the amount field should
be a 10-digit right justified, zero filled, implied 2 pos decimal field. So
I need the amount field to export as "0000987715".
In the query I will be exporting, I multiplied 9877.15 by 100 and formatted
the field as "0000000000" (that is 10 zeros). The query output gives me the
correct format: "0000987715"; however when i export it using a spec file,
whether from the Export menu or using TransferText, I lose the leading zeros
and get : "987715.00"

How can I preserve the format i would like: Zero filled with implied 2 pos
decimal? Any help will be really appreciated!
 
You probably need to covert things into a string instead of formatting it
with leading zeros. Formatting how things look as opposed to how things are
actually stored. Messes me up all the time.

The String() function can be used to pad with leading zeros once it knows
how long the string is. Therefore something like this will work:

Debug.Print String(10-Len(CStr(9877.15 * 100)),"0") & CStr(9877.15 * 100)
= 0000987715

Something like this should work with the proper field names between the
brackets.

PaddedString: String(10-Len(CStr([TheField]* 100)),"0") & CStr([TheField]*
100)
 
Thank you Jerry for your help. Something interesting happened: I used the
String function you suggested in the Query I need to export, and exported
using the same spec file I had defined with my original query (that had the
amount field as numeric). The same thing happened: although the query output
shows the correct formatting, once I export the query to a txt file, I lose
the formatting and get the following: "987715.00" instead of "0000987715". I
even changed my query to a make table query and saw that the field in the
table is now text, then when I exported the new table, I lost the formatting.

Then, I redefined the export spec file based on the new table where the
field was a text field, and it worked! Now, exporting the query (with the
String() function) using the new spec file works as well. So apparently,
when defining an Export Spec file, although we cannot explicitely specify the
type of each field, Access internally assigns a type for it based on the
query or table we are exporting at the time. Then, even if we change the
type of some fields in the query or table, if we use the same spec file,
Access uses its pre-determined field type. Interesting!

Anyhow, thank you Jerry. I appreciate your kind help.

Jerry Whittle said:
You probably need to covert things into a string instead of formatting it
with leading zeros. Formatting how things look as opposed to how things are
actually stored. Messes me up all the time.

The String() function can be used to pad with leading zeros once it knows
how long the string is. Therefore something like this will work:

Debug.Print String(10-Len(CStr(9877.15 * 100)),"0") & CStr(9877.15 * 100)
= 0000987715

Something like this should work with the proper field names between the
brackets.

PaddedString: String(10-Len(CStr([TheField]* 100)),"0") & CStr([TheField]*
100)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


MarieT said:
Hello,

How can I export an amount field in an Access table to a fixed length text
file without losing the leading zeros and removing the decimal point (having
an implied decimal point)? The Export Spec file does not allow me to specify
the type of the field I am exporting. Here are the details:

I imported an amount field into a new table using a spec file where I
defined the amount field as a double. The fixed length input file has the
amount field in the following format: 9877.15 (with no leading zeros, and the
length of the field being 15).
I have to export the info in my table to another fixed length text file with
the information formatted differently. Specifically, the amount field should
be a 10-digit right justified, zero filled, implied 2 pos decimal field. So
I need the amount field to export as "0000987715".
In the query I will be exporting, I multiplied 9877.15 by 100 and formatted
the field as "0000000000" (that is 10 zeros). The query output gives me the
correct format: "0000987715"; however when i export it using a spec file,
whether from the Export menu or using TransferText, I lose the leading zeros
and get : "987715.00"

How can I preserve the format i would like: Zero filled with implied 2 pos
decimal? Any help will be really appreciated!
Click to expand...
Click to expand...
 
Back
Top