One product's (or person's) intended design is another product's (or person's) bug. And vice versa.

by Michael S. Kaplan, published on 2010/05/03 07:01 -04:00, original URI: http://blogs.msdn.com/b/michkap/archive/2010/05/03/10002241.aspx

It is interesting how often there will be two groups of people, each of whom thinks a particular behavior is expected, while another is buggy.

The funniest part happens when one group is more right than the other, by some objective criteria like overall behavioral impact.

As a final plot twist, a few inconsistencies and confusing KB articles can fan the flames, as well.

Jan is 100% correct in his concern -- Excel's behavior here that would simply format using the Regional and Language Options formatting without regard for the fact that the intrinsic meaning of the data is changing boggles the mind as something that would quite simply corrupt currency information completely if someone doesn't know to ignore the currency sign completely.

The only time the behavior is not a complete brain-dead bug is when an Excel application is empty of data and shipped to a customer with different settings -- that would the one time that the change shouldn't retain any sense of memory of the fact that you typed in krowns for those monetary values when perhaps you wanted euros. But if you have any data in the spreadsheet, it should retain that memory of what the values were when you typed them in. Any other behavior is potentially very very bad unless you ignore the formatting -- in which case you are better off if it isn't there at all.

There was a scene in TV show Angel where Angel, after having won a certain ring, had earned the right to go to the home office of Hell. He went down a long elevator ride and came out where he started -- because he was already in Hell but had not noticed.

Imagine for a moment that the majority of the business world is run on various Excel spreadsheets.

Because for every application a company pays some consultant to write, there are four dozen Excel spreadsheets that contain all kinds of bsuiness logic in them.

Imagine that just 0.1% of them hold currency values where a reported printed with a changed currency symbol but unchanged values could cause companies to think that THEY were now in Hell.

Luckily, this really is just a display issue; the symbol imparts no knowledge that would cause programs to behave differently. So beyond a few reports that print values that look to insanely large or too laughably small, the "bug" is more or less contained.

Unfortunately Jan, Excel has no solution here; this is their long-standing behavior.

You basically need to treat it as a number and ignore the currency sign, storing that knowledge elsewhere of whether you are dealing with dollars or euros or yen or whatever. Which is what people mostly do.

So if you look at the nature of the bug here, to fix it on the Excel side you would have had to have each field formatted as a currency value store not only that it is a currency value but also what the Regional and Language Options locale was at the time the value was added.

But a related bug (when you change the default user locale, say from en-US to ja-JP) that Excel also has -- and is very serious if you think about the way currency rates fluctuate and are sometimes so very different that you are massively changing the meaning of the numbers -- that bug does not exist in all programs.

If you look at Microsoft Access, it turns out that at least since Access 2.0 and probably before that it has been doing the very "fix" I suggested above: when you set a column's formatting to be Currency it remembers the default user locale when you put in the data. So that if you change your default user locale it does not massively change the meaning of your data.

If there are any Access old-timers around they could verify my recollection on this one, but it was largely through the efforts of Emma, the International Program Manager for Access from Access <= 97 that kept this subtle data corruption bug from being added to the product.

It was intentional, it was by design, and has been for at least since Access 2.0 and maybe earlier.

But you may notice if you look at Microsoft Access and the Ten-Year-Old Currency Bug from a bit over a year ago that Donn Edwards (the author) not only believes the behavior of "silently" making this change to be a bug, but he blames me personally for the behavior.

Even though it was done before I was even doing things with Access as a user in any serious way, let alone on the Access team (and that when I was on the team years later I was not working in that area of the product).

Okay, I'll take one for the team here despite all that, since I have defended the behavior in the past, am defending it right now, and will continue to defend it in the future -- it is the right behavior. Though may still be some nuances for the empty database scenario -- and if there still are I'd be willing to call those bugs since the empty table case reasonably could be expected to behave differently from the case where a table contains rows of currency data -- which I assume is the one I am being vilified for (though the rant goes on for quite a while so I tuned out; perhaps someone else can glean whether there is another complaint there).

To be honest it would make sense to extend the Access behavior to handle Jan's case where the currency within a locale is changed (e.g. from krowns to euros) and store it the same way that the locale is stored now. Because that would help with the migration of existing monetary data. I suspect that making such a change would be controversial, though if I am right everyone could be made happy with some simple changes in the next version of Access (after the one that just came out) to clean up any remaining issues in the empty table case (if there are any; I have not checked).

Then someone could also fix the crazy behavior in Excel, the one that does corrupt data unless one knows to ignore the formatting....

I agree that the documentation here is absent/confusing, and with some of these vicious threads the web isn't much better. Also, this retired KB article is wrong about the old products to which if refers. Not sure what gets done in that case. This other article is a more accurate description of the behavior.

And I have a lot more respect for MVP Allen Browne's take on the issue; he and I disagree, but a lot more respectfully. He at least takes the time to understand the intent and feels that the documented descriptions of the application lead one to make bad decisions since the behavior is not clear. It if for that sake that I hope Access would consider looking into fixing the empty table case (perhaps on Compact, Access could reset the format when there are no rows in the table, or something like that? It is more complicated than that since it would be a behavior change, but an "application deliver" process would be a new feature that could perhaps do this kind of cleanup).

It is interesting to find out that the Internet contained a page with such venomous indictments of me and I never knew about it at all. But I suppose that is the nature of the angry dark side of the web....

"Now as a by the way, no product does this right."

The now-defunct Microsoft Money came very close to doing this right. It was inherently multi-currency, so it didn't respect the current Locale as you describe, but it did remember what currency each amount represented, and it had some special-case code to handle a country's conversion of its currency.

-Russ Paul-Jones

Ah, very true. Of course they lost in face of Quicken (who else imagined a Higjhlander-esque "Quickening" pun in a headline on that one -- there can be only one!)....

If I had thoght about that one, that would have been a theme here and the title for *this* blog. :-)

I'm not saying it's not lame - I'm saying I can't see how it's Unicode-related. Usually posts in this category have to do with some flaw in the standard itself, or some flaw in support for it in some program.

Hi all, thanks for explaining. There was this little hope that if you can actually choose the currency the Excel will use (even "CZK"), it does remember the choice by means of format, not of culture. Especially when we have custom cultures now and one cannot guarantee the very one used will be present on the other system.

However, it is true that if I am using Czech culture modified to use a dot as a decimal separator, I would still expect that data will be formatted according to the settings on the computer showing it. Interesting. (this also cancells the culture+date solution)

And it is true that if there were some cells in krowns ..or krowns in cells :), I would expect that entering new numbers will still use krowns, which is not what could saving the currency format per value deliver anyway.

Hmm..so it turns out I have no opinion how this should work, but I don't like any of the obvious ways and non-obvious would be... well not obvious, anyway.

What a hard work an internationalization team has to do! :)