Currency Symbols on MS Access Reports

[Zwoker]

Hello everyone,

I have an MS Access 2003 application that is distributed as an MDE.

All the users around the Asia/Pacific region that use it are running Windows XP.

Many of them have different currency symbols in use, and these are declared on their regional settings in the control panel on their PCs. But despite this all the currency fields on the reports are appearing with a leading dollar symbol - which is the default for my PC, which the MS Access development was done on.

Can anyone suggest something that I can do to make the reports obey the PC regional setting they are running on? From seeing copies of the printed output I know it is using the correct local date formats, but all the fields on the report (declared as type currency) are ignoring it.


Thanks.

[NeoPa]

Presumably these are TextBox controls that you have on the report. What is the value of the Format property on these TextBox controls on your report?

[Zwoker]

Presumably these are TextBox controls that you have on the report. What is the value of the Format property on these TextBox controls on your report?

It is the Currency format, chosen from the pick list that appears when you click on the end of the format field in the textbox properties. I haven't defined my own format or anything.

Is there some sort of environment-override that I have accidently turned on that would force the "$" to always be displayed instead on the users PC regional settings currency symbol?


Thanks.

[NeoPa]

Not that I'm aware of I'm afraid :(

I don't play much with different currencies (ironic as I was once doing support on an FX Dealing Floor), so I don't really have the relevant experience here. It's clearly not my first guess as you have that set up as recommended.

It's hard to notice potential problems even, when you don't have the databae in front of you.

[Lysander]

Just found this link via google that explains your problem

http://allenbrowne.com/ser-44.html

It starts as follows


Currency format
If you can set the Format of a field or text box to "Currency", Access does not store this setting and does not respect the Regional Options of the user.

Secretly, Access stores the literal value of your currency settings, and only displays the word "Currency". The deception is discovered if you change your Regional Settings: suddenly all the formats no longer read "Currency", and the hard-coded value of your previous currency setting is revealed.


The writer also gives a workaround
"Workaround
To develop an application that does correctly adapt to the user's currency settings, you must reset the Format property of every affected control every time you open a form or report. Use the Open event to reassign the word "Currency" to the Format property each text box, combo, etc.
"

All credit to Allen Browne for the above

[NeoPa]

Nice find Lysander :)

That is such a heap... though, from Microsoft. Such amateur logic. They should provide a fix for this. I'm SO unimpressed.

[Zwoker]

Thanks Lysander.

I had googled for an answer but not found anything meaningful.

That link explains my problem exactly. I'll try the suggested work around and see how it goes.


Regards,
Zwoker.

[ADezii]

Private Sub Form_Open(Cancel As Integer)
On Error Resume Next
 
Dim ctl As Control
 
'Write Convert in the Tag Property for each Control
'you wish to set the Format Property to Currency
For Each ctl In Me.Controls
  If ctl.Tag = "Convert" Then
    ctl.Format = "Currency"
  End If
Next
End Sub

[Zwoker]

Thanks ADezii,

That works great. I had already manually typed in the manual format code for all the currency fields for one of my smaller reports, and it worked fine.

I just tried your code in one of the larger reports after multi-selecting all the currency fields and giving them the tag, all in one big go, and it all worked just the same, but with a lot less effort.

I'll be able to convert all my reports to use the true currency symbol in just a few minutes, rather than a few hours.

Woo Woo! *grin*

[NeoPa]

It's just occurred to me that I did know something related to this (I didn't make the connection though I'm afraid). While working with formats in Excel a while back I was surprised to find that when I'd set it to currency it was actually remembering it as the full string ("£#,##0.00;[Red]-£#,##0.00") rather than simply as "Currency".

[JustJim]

Hi guys,

I've been subscribed (OK, lurking) on this thread from the start, and I agree from the Micro$oft side it is a crock of shirts.

Please tell me though that there is an underlying agreement that the 'Control.Value' is being adjusted by some conversion factor and that that conversion factor is being updated either manually or from the wwweb.

There would be no point, surely, in changing $100 in the display to ₤100 or €100, that just wouldn't make sense.

Or am I missing something?

Jim

[Zwoker]

Hi,

As a general background FYI on the last comment - my reports run in two modes.

1) Just extract data for the users own location.

In this situation, all the currency data read from the backend database is in the users own units, but was (until yesterday) showing with a leading dollar symbol on all the printed reports, regardless of the users true currency symbol.

In this situation no conversion is required.


2) Extract data for the whole Asia/Pacific region.

In this situation I do a currency conversion of the local currency to a user selected currency (usually US dollars). It was somewhat ironic that this was working fine as the dollar symbol was appropriate when printed on the reports.


Regards,
Zwoker.

[NeoPa]

Or, to put it another way Jim, the value in the field would never change due to this issue. This is not a convertion situation at all. The display of the currency symbol itself is simply done wrong (essentially a number formatting issue). Although this would, yes, result in completely inappropriate and misleading values if interpreted in the currency displayed, it doesn't involve any manipulation of the data as such by Access.

EG. Assuming Cable (£:$) is running at roughly 2:1 and a value of £100 is being displayed, this would show as $100 rather than a converted figure of $200.

[JustJim]

Fair enough, thanks for taking the time to satisfy a nosey parker!

Jim

[NeoPa]

Curiosity may have killed the cat Jim, but without it, human knowledge would probably just fail and die :)

[JustJim]

Curiosity may have killed the cat Jim, but without it, human knowledge would probably just fail and die :)

The cat was innocent, innocent I tell you!