Microsoft Access does not format currency properly when the computer's region has changed.

by Michael Kingham (posted October 2007)

The Problem

If you create a form or report in MS Access and assign 'Currency' to the format property of the field, Access will use the current region's currency settings to display the number. (That part works just fine!)

Now, if you move the database to another PC with a different region or change the region on the original PC, Access does something very unexpected. It will alter the format property on that field from 'Currency' to a hard coded representation of the format of the original region. (in the US it changes the format property to '$#,##0.00;($#,##0.00)') . The net result of this action is that the field will continue to display the number in the original region's currency format instead of the currently selected region's format. (see a step-by-step guide to reproducing the problem)

This is the exact opposite of how dates are handled. If you format a field with 'Short Date' and change the region's date format (maybe mm/dd/yyyy US to dd/mm/yyyy UK) the form /report will immediate adopt the new regions date format and it will not alter the format property of the field. (click here to see an example of this)

A really bad reason why

After reading a lot of Microsoft documentation on this, they never come out and admit that the Currency format works this way. In fact, their documentation says just the opposite.

From Access 2003 help file:

But in a round about way they hint that this behavior has justification because $100 US Dollars is not equivalent to £100 British Pounds. So if a database is created in the US and has data in it (say a pricing database) and it gets opened in the UK, if all the Forms and Reports showed the US prices with a Pound symbol in front of them, the prices would be wrong.

While this may be true, it makes it hard for those of us that develop applications in MS Access to sell our software to international clients.

The Solution

If at runtime, you reassign 'Currency' to the format property of a form or report, it will adopt the local region's currency format and display currency correctly.

To do this by hand, you could put this code in the 'On Open' Event of a Form or Report:

Me.Field1.Format = "Currency"
Me.Field2.Format = "Currency"
Me.Field3.Format = "Currency"

This would be a maintenance nightmare over time, so I wrote a routine that loops through all the controls on a report and if they have the hard coded format property (an indication that this database is being run in a region different that the one it was developed in), it resets the format property to 'Currency'.

This will allow the currency fields on the report to display their data using the current region's currency format.

Public Sub SetRegionalCurrencyFormat(MyReport As Report)

  For Each ctl In MyReport.Controls
    For Each prp In ctl.Properties
      If prp.Name = "Format" Then
        If Len(prp.Value) > 0 Then
          If Left(prp.Value, 1) = "$" Then
            prp.Value = "Currency"
          End If
        End If
      End If
    Next prp
  Next ctl

End Sub

To call this routine, put this line in your Report's 'On Open' Event:

Private Sub Report_Open(Cancel As Integer)
  Call SetRegionalCurrencyFormat(Me)
End Sub