Friday, October 21, 2011

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):
SettingDescription
General Number(Default) Display the number as entered.
CurrencyUse 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