Access to Excel currency field loses decimal formatting

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

Guest

I have a number of queries, when exported to Excel, lose their decimal number formatting.

I have formatted the underlying tables to show format as currency and decimal place as 0, and have entered properties on all queries on the currency fields to show 0 as the decimal property. When running the query in Access the output looks great. However when exporting to Excel the decimal property reverts back to 2

Is there a way to overcome this
All replies are appreciated

Regard
Margaret
 
i tried a couple things.
Format([money],"$#,###")
Format(CLng([money]),"$#,###")
both exported without the decimal values, but the values in the Excel file
were text, not number.
CLng([money])
exported as number values without decimals, but beware. if you have odd
cents on any of your values in Access, CLng() will round them up or down to
the nearest whole number.

hth


Margaret said:
I have a number of queries, when exported to Excel, lose their decimal number formatting.

I have formatted the underlying tables to show format as currency and
Click to expand...
decimal place as 0, and have entered properties on all queries on the
currency fields to show 0 as the decimal property. When running the query
in Access the output looks great. However when exporting to Excel the
decimal property reverts back to 2.
 
Thanks Tina, but I am not sure where I am supposed to key in this formatting string. The amount comes down from specific imports I have set up and they are all marked as Currency. Where can I go to choose a currency format that indicates no decimal places

If you could point me in the right direction of where to enter the format strings you have indicated, I will give it a try and see if it works

Thank
Margaret
 
open the export query in design view. in the QBE grid (rows/columns in
bottom half of window), look at the top row (Field:) across each column
until you find your "money" field. then replace the field name with the
expression you want to try out. note: access will automatically assign a
field label as "Expr1:". so the new value in the Field: row of the money
column will be (for instance)

Expr1: Format([money],"$#,###")

and, of course, you have to replace the word "money" with actual name of the
currency field.

hth


Margaret said:
Thanks Tina, but I am not sure where I am supposed to key in this
Click to expand...
formatting string. The amount comes down from specific imports I have set
up and they are all marked as Currency. Where can I go to choose a currency
format that indicates no decimal places.
If you could point me in the right direction of where to enter the format
Click to expand...
strings you have indicated, I will give it a try and see if it works.
 
Thank you very much. I have put this expression in and received the correct answer
Appreciate your hel
Regard
Margaret
 
Back
Top