Microsoft Access Tips for Serious Users

Provided by allenbrowne.com, 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):

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.