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?
Fortunately