Export Currency to a Flat File

[KFoster]

I have imported an Excel spreadsheet containing text and currency data. I
define the currency data as number with two decimal places. when I export to
a TXT file, the numbers export with two decimal places (as I have specified
in the Access file), but when the number is something like 34.10, it exports
as 34.1. A number like 345.00 exports as 345. I must have exactly two
decimal places in the export. How do I force the formatting: to produce
exactly 2 decimal places and have no "," separators each 3 digits?

 

[Douglas J. Steele]

Create a query that uses the Format function on that field, and export the
query, not the table.

 

[KFoster]

I created the query you suggested and used the FormatNumber(Fieldname,2) to
force two decimals. That part worked fine, but the resulting exported file
has "," separators between 000,000 as such. I could change my regional
variables, but if someone else ran this on their computer, it would yield
different results. Now how do i get rid of the "," separator between
thousands. Thanks for the good suggestion. I would not have been my first
approach but it worked fine up to the final formatting change noted above.

 

[Douglas J. Steele]

Format(FieldName, "#.00") should do it.

 

[KFoster]

fieldname: Format(FormatNumber([numberamount],2),"0.00") Got the results I
needed. This statement was coded into the query's column name for the field
on which I wanted to force specific formatting.

Thanks,

 

[Douglas J. Steele]

No need for both the FormatNumber AND the Format statements. The Format
statement I gave should have been sufficient.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

fieldname: Format(FormatNumber([numberamount],2),"0.00") Got the results
I
needed. This statement was coded into the query's column name for the
field
on which I wanted to force specific formatting.

Thanks,

Format(FieldName, "#.00") should do it.

 

[KFoster]

I changed per your suggestion and the Format command works perfectly:

Format(FieldName, "#.00")

1. Exactly 2 decimal places even if the number ended in ".10", or ".00".
2. No commas to separate thousands. (i.e. "000,000,000.00" as "000000000.00")

Thanks, KFoster

No need for both the FormatNumber AND the Format statements. The Format
statement I gave should have been sufficient.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

fieldname: Format(FormatNumber([numberamount],2),"0.00") Got the results
I
needed. This statement was coded into the query's column name for the
field
on which I wanted to force specific formatting.

Thanks,

Format(FieldName, "#.00") should do it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I created the query you suggested and used the FormatNumber(Fieldname,2)
to
force two decimals. That part worked fine, but the resulting exported
file
has "," separators between 000,000 as such. I could change my regional
variables, but if someone else ran this on their computer, it would
yield
different results. Now how do i get rid of the "," separator between
thousands. Thanks for the good suggestion. I would not have been my
first
approach but it worked fine up to the final formatting change noted
above.

:

Create a query that uses the Format function on that field, and export
the
query, not the table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have imported an Excel spreadsheet containing text and currency
data.
I
define the currency data as number with two decimal places. when I
export
to
a TXT file, the numbers export with two decimal places (as I have
specified
in the Access file), but when the number is something like 34.10, it
exports
as 34.1. A number like 345.00 exports as 345. I must have exactly
two
decimal places in the export. How do I force the formatting: to
produce
exactly 2 decimal places and have no "," separators each 3 digits?