Currency format does not reflect regional settings

        4 votes: ***       7,803 views      1 comments
by Allen Browne, 20 April 2005    (Access 2+)

Microsoft Access Tips for Serious Users

Provided by Allen Browne, January 2004


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.

To demonstrate this issue:

  1. Create a table with a field of type Currency.
  2. In the lower pane of table design view, set the Format property to "Currency".
  3. Save the table, and close the database.
  4. Open the Windows Control Panel. Go to Regional Options, and change the Currency setting. For example, if you are in the US and using Windows XP, on the Regional Options tab of the Regional and Language Options box, change the top drop-down box to "English (United Kingdom)".
  5. Open your database again. Open your table in design view. The Format property of your field has changed to a literal string representing your previous setting.

Perhaps the Access designers thought this was clever: that a database with American dollars would still show dollars when opened in Italy or Israel. In my view, it makes no sense for Access to tamper with the settings we assign, i.e. if we hard-code settings they should remain intact, and if we use the adaptable setting "Currency", then Access should adapt. As it is, we are denied both choices.

All versions of Access work this way. The behaviour is counter-intuitive, inconsistent with the rest of regional settings, and contradicts the documentation. For example, the Access 2003 Help under Format Property - Number and Currency Data Types includes this (emphasis added):

Setting Description
General Number (Default) Display the number as entered.
Currency Use the thousand separator; follow the settings specified in the regional settings of Windows for negative amounts, decimal and currency symbols, and decimal places.

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.

For example, in the Orders form of the Northwind sample database, you need:

    Private Sub Form_Open(Cancel As Integer)
        Me.Subtotal.Format = "Currency"
        Me.Freight.Format = "Currency"
        Me.Total.Format = "Currency"
        With Me.[Orders Subform].Form
            !UnitPrice.Format = "Currency"
            !ExtendedPrice.Format = "Currency"
            !OrderSubtotal.Format = "Currency"
        End With
    End Sub

Yes, it becomes tiresome. Maintenance is the greater problem: you must consider this issue with every change to a form or report in an internationalized application.


Home Index of tips Top

Rate this article:   Your rating: Poor Your rating: Not so good Your rating: Average Your rating: Good Your rating: Excellent


This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Currency format does not reflect regional settings'?


1.

Donn Edwards says...

26 March 2009

 
This annoying bug still exists, even in Access 2007 SP1. See
http://donnedwards.openaccess.co.za/2009/03/microsoft-access-and-ten-year-old.html

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments
Verify Code
Verification Code


It may take 30 seconds after clicking submit - please be patient to avoid duplicate submissions.  

All comments must be approved before being displayed on this web page.  This process may take up to 24 hours.