By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,052 Members | 1,456 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,052 IT Pros & Developers. It's quick & easy.

Formatting Currency Values

P: 37
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.
Jan 13 '10 #1
Share this Question
Share on Google+
18 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Jan 13 '10 #2

NeoPa
Expert Mod 15k+
P: 31,494
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.
Jan 13 '10 #3

P: 37
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.
Jan 15 '10 #4

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Jan 15 '10 #5

P: 37
that also has $#.##0,00;($#.##0,00) set as its format
Jan 15 '10 #6

NeoPa
Expert Mod 15k+
P: 31,494
I'm afraid I'm unable to reproduce what you describe Max. Even in a report. It always shows as dollars for me.
Jan 17 '10 #7

P: 37
Do you think i need to code and set field format as $#.##0,00;($#.##0,00) on form load
Jan 18 '10 #8

TheSmileyCoder
Expert Mod 100+
P: 2,321
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?
Jan 18 '10 #9

P: 37
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
Jan 18 '10 #10

TheSmileyCoder
Expert Mod 100+
P: 2,321
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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 18 '10 #11

P: 37
but there is no name set for the charts which are on their respective forms
Jan 18 '10 #12

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Jan 18 '10 #13

P: 37
Sample file is attached..Attachment 2634
Jan 18 '10 #14

TheSmileyCoder
Expert Mod 100+
P: 2,321
*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_finalRender)
Expand|Select|Wrap|Line Numbers
  1. Me.Form.ChartSpace.charts(0).axes(1).Numberformat = "$ #,##0.00"
One thing I noticed, was that If I wrote:
Expand|Select|Wrap|Line Numbers
  1. 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.(Cause the helpfile was non-existant on ChartSpace and ChartObject)

I hope you find a solution.
Jan 18 '10 #15

P: 37
I tried to develop a chart using the wizard and i tried to set the format when i created the form, there is no way i could alter the format of the fields. it is still reverting back to the way it is. as u did i tried to code using the event, but i keep getting some error or the error. I have been stuck with this since friday.. All the time i saved in the save confirmation dialog is getting wasted here.
Jan 18 '10 #16

NeoPa
Expert Mod 15k+
P: 31,494
Just as a tip, if you want the character value $ to appear, independently of it's expected effect as a currency indicator, you can use \$ (within a format string).
Jan 18 '10 #17

P: 37
i tried format the fields with \$ but it still takes the regional settings
Jan 19 '10 #18

NeoPa
Expert Mod 15k+
P: 31,494
I'm sorry. I wish I could help more (or even at all really), but I cannot reproduce your problem. It all seems to work for me (& I'm UK based so $ is not my default currency either). The only time I ever recall seeing something like this was in Excel, but I don't recall the details. I'm particularly surprised the \$ thing would be converted.
Jan 20 '10 #19

Post your reply

Sign in to post your reply or Sign up for a free account.