Formatting Currency Values

[maxpirate]

The currency values are displayed in format of the regional settings specified in the computer of the user. I would like to display all the currency values in dollar format irrespective of the regional settings. i tried formatting the currency columns as $#.##0,00;($#.# #0,00) but it still shows values in regional formats.
Is there a way to specify an application specific setting.

[MMcCarthy]

As far as I'm aware you can only set the custom format with the full stop as the decimal point and the comma as the thousand separator as follows:

$#,##0.00

[NeoPa]

Exactly where and how did you try setting this currency format?

Everything I tried showed the values in dollars. My settings are set in standard British format with the £ (GBP) as the currency symbol.

PS. Though many European countries use the format you show (. & , reversed from the way used in Britain & America), this would be unusual for a display of dollars (I suppose it may be what they use in those countries when showing Dollars. I'm not really sure). I tested and I found my PC settings determined how each character is interpreted - regardless of where you put it in a format string. This doesn't look promising I'm afraid.

[maxpirate]

The format i specified has been set for all currencies in the tables.
When i view the data in table it show as dollars only but when i view the data in my reports the charts are taking up regional setting currency symbol which is GBP.

[TheSmileyCoder]

If the reports were made before you added the formatting to the table, they wont "inherit" the format value. You can manually assign it in the report, by looking at the textbox's format property.

[maxpirate]

that also has $#.##0,00;($#.# #0,00) set as its format

[NeoPa]

I'm afraid I'm unable to reproduce what you describe Max. Even in a report. It always shows as dollars for me.

[maxpirate]

Do you think i need to code and set field format as $#.##0,00;($#.# #0,00) on form load

[TheSmileyCoder]

As far as I know, if you have set it correctly it should work.

If its still not working, then maybe you made a typo?

Is there any code attached to the field?

[maxpirate]

What is happening is there is pivot chart that has a query as a source. when i formatted the currency fields in the query to be displayed as dollars. after that when i saw the query result it is as expected.
When i see it on the pivot chart view inside a form its taking the regional setting format.
when i view the same as datasheet the values are formatted as $$$ as expected

[TheSmileyCoder]

Wow you could have saved us all ALOT of trouble if you had specified it was within a chart. A chart is an object within access, with totally different behavior then a textbox for instance (who would have guessed....)

On your report open event have code like this, assuming your chart control is named ctrlChart:

ctrlChart.Axes(xlCategory).TickLabels.NumberFormat="$ #,##0.00"

You may need to exchanged xlCategory with xlValue depending on which axis your working on.

The easiest way to learn how to manipulate charts in Access is (IMO) to open Excel, create a similar chart, then start the Excel macrorecorder, do your changes, switch to VBA and view the code generated.

[maxpirate]

but there is no name set for the charts which are on their respective forms

[TheSmileyCoder]

Be sure you have selected the chart object or "container" if you will, not the object inside the container. It will likely be named something like Chart1 or chartXX.

If you still are having trouble, Id suggest attaching your DB here and I or someone else can take a look at it.

[maxpirate]

Sample file is attached..

[TheSmileyCoder]

*DEEP DEEP SIGH*

I've spent nearly 2 hours now, and I give up. Ive found the property, and tried to set it at different points in the events (Like form load, form current, and the pivotchart events)

Sometimes I can set it, sometimes I can't.

When I CAN set it (Meaning I don't get an error), it has no real "Lasting" effect.
When I can't set it, I get a error saying cannot set this property during a event procedure (Such as the Pivotcharts Before_Render, After_Render, After_finalRend er)

Me.Form.ChartSpace.charts(0).axes(1).Numberformat = "$ #,##0.00"

One thing I noticed, was that If I wrote:

Me.Form.ChartSpace.charts(0).axes(1).Numberformat = "'$' #,##0.00"

the formatting in the chart would look like 'kr' 25,000.00, the ['] would carry over to the formatting, but it would still go regional (kr is danish currency). Dunno if you can use that info for anything.


I did learn 1 thing from browsing the net for a solution, Variable watching. If/when it fails and you enter debug mode, you highlight the ChartSpace and press "Shift-F9" you can add a watch on that variable. Next time you load the form and it fails again, you can get alot of detailed information about the ChartSpace objects, and its exposed properties.(Cau se the helpfile was non-existant on ChartSpace and ChartObject)

I hope you find a solution.