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" wrote:
> 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" wrote:
>
> > 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!
|
Similar Threads
