Thursday, March 26, 2009

Microsoft Access and the Ten-Year-Old Currency Bug

Today I wasted hours of valuable time fighting with a bug that is in Microsoft Access 97 and can also be found in Microsoft Office Access 2007. Does it really take more than 10 years to fix a bug like this, or is Microsoft just deaf? You decide.
This is how you get the bug to work:
1. Change your regional and language settings to "English (South Africa)" so that your currency fields use the "R" symbol.
2. Create a table with some fields in it. Include two currency fields, Field2 and Field3.
3. Populate the table with a few rows of data
4. Create a report, and make sure that the format of Field2 and Field3 is set to "Currency".
5. To be extra sure, include the following code in the Report Open event:
Report!Field3.format = "Currency"
6. Preview the form. Close the database.
7. Change your regional and language settings to "Zimbabwe", so your currency is now "Z$".
8. Open the database, create a form, and put the currency fields on the form.
9. Make sure both currency controls have their format set to "Currency"
10. To make extra sure, put the following code in the form open event:
form!Field3.format = "Currency"
11. Put a button on the form to preview the report.
12. Set the database to open the form on opening the database.
13. Preview the form.
14. Close the database, and change the regional and language settings to "English (Ireland)"
Theoretically, all the controls with the Currency format should show their values in Euros, the currency for Ireland. Wrong.
As you can see from the screen shots shown here, Access converts the format from "Currency" to "R #,##0.00;R-#,##0.00" for the fields that were edited when the settings were South Africa, and "Z$#,##0.00;(Z$#,##0.00)" for the Zimbabwe settings. Only if you use code to force the control to retain the "Currency" format, does the formatting work correctly. That's why Field3 displays correctly but Field2 does not.
Why does it do it? Because Access is written by programmers in Redmond, USA, who seem to think that Canada and Mexico are on other continents, and don't realise that Redmond is not the centre of the universe and that most of the planet does not format its dates as mm/dd/yyyy. But that's another bug.
Now you can change your currency settings back to what you normally use, and shake your head in disbelief. This bug works in all version of Access from 97 to 2007. It was reported in January 2004 applying to "Access 2 and above" by Allen Browne. I guess it might be fixed in a decade or so.
Update Friday 27 March: I tried doing a loop through all the report controls, but you can only do that in design view, not in print preview, so I used the Tag property and stored the names of all the Currency controls in a tag item. Then I use the OnOpen function to set the format of the explicitly listed controls. I use a similar technique for forms. This bug (or is it an undocumented feature?) has cost me 2 days worth of programming time. Thanks for nothing, Microsoft! Both of these bugs are not documented anywhere on the Microsoft site or in the Access documentation that I could find.
Update Saturday 28 March: Setting the currency fields in the table to have a format of "Currency" doesn't work either. It seems that anything done at design time is likely to fail. What's worse is that Access lies about the format setting. It will display the format property as "Currency" when it isn't stored that way. You can expose this lie only by changing to a different region, and then inspecting the format property again. By then it's too late because the app is already installed in another country on a customer's machine, and they are yelling at you on the phone. Access 2007 also has a format called "Euros" just to add to the confusion.
The code I use in the OnOpen event is basically this
'
'// Fix up CURRENCY formatting
'
Dim strField as string, strTag as string, n as long
strTag = Nz(form.Section(acDetail).Tag, "")
If Len(strTag) > 2 Then
strTag = strTag & ","
n = InStr(1, strTag, ",")
While n > 0
strField = Mid$(strTag, 1, n - 1)
strTag = Mid$(strTag, n + 1)
form(strField).Format = "Currency"
n = InStr(1, strTag, ",")
Wend
End If
This code relies on the tag in the form detail having a list of fields, separated by commas, that require correct currency formatting. Similar code works in the OnOpen event of a report.
One final thought: I don't hate Microsoft, or Access. I have used Access 97 almost every day since I bought it in 1998 and installed it on Windows 98. Access is awesome, in spite of these annoyances. As a programmer I am in awe of all the things it does. Why they have to spoil it with undocumented nonsense that persists for 10 years is incomprehensible to me. I guess geeks aren't perfect either.
Update Sunday 29th March: Wayne Phillips sent me another elegant solution. I haven't tried it yet but it looks promising. I am al;so dismayed to find that the same bug exists in Excel, so I guess it's never going to be fixed, or documented.
Update Wednesday: Here is a comprehensive list of similar Access "gotchas" (i.e. Momentary Lapses of Reason on the part of Microsoft). I cannot believe the arrogance of these people. And as for the Dollars vs Lira argument, I can't believe that ANY competent programmer would have a "Currency" format that was not hard wired to $ or Lira if the possibility of confusion could arise. Microsoft themselves have now introduced a "Euros" format for precisely this problem, but have left the old "Currency" behaviour permanently broken. PEBCAK indeed: the only debate is whose chair and keyboard? I say Michael Kaplan's chair and keyboard, not mine. And when are they going to WTFM to document it correctly?

12 comments:

Anonymous said...

That's why i only ever show currency on the form, not in the table/query. The date is also a stupid headache.

Thanks for sharing this :)

Wayne Phillips said...

Please disregard my previous comment - I've now found a _much_ better way to do this - and it's so simple too. Read my new article here:

Using the Currency field data type - without the hassle

Hope this helps.

Igor Levicki said...

You should try contacting Michael Kaplan, perhaps this Access bug has something to do with localization (or globalization) and regional settings. He might be willing to look into it.

Wayne Phillips said...

@Igor:

Michael Kaplan has already commented about it in 2003 in the newsgroups and doesn't consider it a bug.

Neither do I, actually - it's largely the help file that is wrong (and the Access UI hiding what it does under the hood by converting "Currency" to the resolved regional setting format, doesn't help either).

http://groups.google.co.uk/group/microsoft.public.access/browse_frm/thread/57671637f2d1e31e/12c70a637278154a?hl=en

Donn Edwards said...

OF COURSE it isn't a bug! How stupid of me. I should have expected Access to lie to me and get the documentation wrong. It does that all the time! What was I thinking?

Donn Edwards said...

Having read Michael Kaplan's arrogant remarks I realise he just doesn't get it, and the person who got this bug introduced without correctly documenting it should be taken outside and shot.

Access already has a method of fixing the currency to a particular format, which is what it does under the hood without warning the user.

The only PEBCAK I can see is between Kaplan's head and the keyboard.

Anonymous said...

2009-1997 is not 10, it's 12! Is this another bug, Donn?

Donn Edwards said...

Er, no. I was comparing Access 1997 with Acces 2007, which is a ten year difference. Bu the bug has been around for over a dozen years, thanks to the woolly-headed logic of Microsoft.

Unknown said...

In my access application The currency values are displayed in the 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 in the table as $#.##0,00;($#.##0,00) but it still shows values in regional formats.
Is there a way to specify an application specific setting. Thank you

Donn Edwards said...

Set the desired formatting in the controls on the forms and reports, not in the data file. Use the sample code but change the word "Currency" to the actual format you wish to use. That should do it.

John Cowan said...

It's not really about the formatting, which is a side issue. It's about the meaning of the data, and currency data is only meaningful if you know what currency it is. So if you are making entries into an Expenses table as you travel about the world, each entry gets properly associated with the kind of money you spent, and remains so even after you come back home. If you spent Z$100 in Zimbabwe, you didn't spend €100 in Zimbabwe, and it would falsify the data to claim that you did. Nor do you want the database to automatically convert the value for you, as it doesn't know as of when the conversion should be made.

Donn Edwards said...

@John: Er, no. That would imply that each *record* would have its own currency, not each field. It would also imply changing my locale to Zimbabwe when I created the Zim field, and then changing my locale to France when I create the Euro field, all in the same table!?

If I wanted to specify which currency to use I would use the correct currency format for the field, such as Euros, Rands, etc.

If I use the *Generic Currency* format, I expect it to use the currency format of the machine running the app, not the currency format of the machine on which the app was developed. This may well be the same, but often it is not.

MS made it worse by adding a "Dollar" format and a "Euro" format, when the $ is used both in Zimbabwe (where the currency has no value whatsoever), Australia, Canada, the USA, etc.

Access currency policies are completely screwed up and illogical, and no amount of justification or "clarification" can hide this clear and obvious bungle, i.e. it's a bug. The same applies to their "mm/dd/yyyy" treatment of all dates.

I have yet to see any attempt at clarifying the policy in Access documentation either, which only makes the problem worse.