Access/VBA Tutorials - Using the Currency field data type

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
http://www.everythingaccess.com

Wayne's recommendations for Microsoft Access developers:

Other articles that may interest you:

Using AutoNumbers properly
Relationships between tables
Data Types in Access

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

Have your say - comment on this article.

What did you think of 'Using the Currency field data type - without the hassle'?

1.	*Ken Owen says...*	24 August 2009
	I may have accidently given you 3 stars when I meant to give you 5. Thank you for providing this solution.

2.	*Wayne Phillips says...*	24 August 2009
	No problem Ken -- I've adjusted it for you. Thanks for your comments.

3.	*Patrick Wood says...*	08 January 2010
	Thanks for the excelent and well written article. I live in the USA and have a client in the UK so this is very helpful for me. I have found that dates can be a problem area also since in the USA we typically use mm/dd/yyyy and in the UK dd/mm/yyyy is the norm.

4.	*Ian Van Audenhaege says...*	30 January 2011
	Thank you for this solution. I live is an English/French area of Canada, so it is important that the currency formatting to always match the current user's regional settings. This solved the problem for me.

Have your say...

Site tools