Using the Currency field data type - without the hassle

Please note: The solution discussed in this article only works for tables, queries and _bound_ form/report controls.

Today I was reminded of the age old problem of the Currency field datatype in Access not reflecting the current users regional formatting settings as set in the Operating System.

The problem is that by default, Access stores the format as specified on the developers machine and does not change the formatting if an end user has a different currency format set in their regional settings.

Some would see this as expected behaviour.  Some would see this as a flaw (or even a bug).  The Access helpfile doesn't help much since the behaviour does not appear to match what is described.

For example, if I use the currency datatype on a table on my machine which has UK regional settings (so pound currency symbol £) and then later sell the application on to someone in the US (with US regional settings), some people would expect the formatting to be shown in dollars on their machine - but that is not the case.

Internally Access interprets the text format 'Currency' and actually stores the resolved format as specified by your regional settings.  So in this case, Access actually stored the Format property as '£#,##0.00;-£#,##0.00'.

This issue is further emphasised by this seemingly wrong behaviour (to many people, anyway) also occurring on forms and reports when specifying the Currency format in controls.

The simple fix (for tables/queries and bound form/report controls only)

If you want your currency formatting to always match the current users regional settings, then fortunately there is an easy fix.

The fix is to still use the Currency data type, but _remove_ the format property in the Access field properties list:

Since you're now not storing any specific Currency formatting, Access uses the current users settings rather than yours.

You can take advantage of this in forms and reports too - but only if you are using bound controls.  To make use of this in forms and reports, you must remove the Format property for your currency controls (leave the property blank) and the format will again be picked up from the users regional settings (as long as you also do the fix from above for the underlying bound field format as well).

Unbound controls

If you use unbound controls, unfortunately you must set the format to "Currency" at runtime using a VBA subroutine. To do this, the simplest option is to loop through each control on the form/report and for any currency control set the format to "Currency". To identify whether a control holds a currency value, you need to either use the Tag property on the control or personally I set the format property to "UserCurrency" in the Access property sheet and then identify them that way instead.

Put this code in a standard module:

Public Sub FixUpCurrencyControls(ByRef FormOrReport As Object)
    Dim Ctrl As Access.Control
    For Each Ctrl In FormOrReport.Controls
        If TypeOf Ctrl Is TextBox Then
            If Ctrl.Format = """UserCurrency""" Then
                Ctrl.Format = "Currency"
            End If
        End If
    Next
End Sub

And then in your form/report, use the OnOpen event to call our subroutine:

Private Sub Form_Open(Cancel As Integer)
    Call FixUpCurrencyControls(Me)
End Sub

Wayne Phillips
www.everythingaccess.com