CODEWITHSUNDEEP
ms-accessvbaregional-settings
gaussblurinc
gaussblurinc

Reputation: 3692

Access + VBA: Currency change. Regional-settings

Access uses regional/country-settings for Currency type.

How to change symbol in Currency type?

Dim DBTable as ADOX.Table
Dim DBCatalog as ADOX.Catalog
Dim DBConnection as ADODB.Connection


DBTable.Name = "TableName"
DBTable.Columns.Append "Currency", adCurrency
DBCatalog.Tables.Append DBTable

Dim C as Double
C = 30.42
DBConnection.Execute "INSERT INTO TableName VALUES (" + "'" + Str(C) + "'" + " ) "

Upvotes: 5

Views: 4739

Answers (2)

Renaud Bompuis
Renaud Bompuis

Reputation: 16796

The Currency data type does not store any currency symbol information. It's just a special numerical type that is best used for accurate money-related storage.

The Access documentation defines the Currency Data Type as:

Currency variables are stored as 64-bit (8-byte) numbers in an integer format, scaled by 10,000 to give a fixed-point number with 15 digits to the left of the decimal point and 4 digits to the right. This representation provides a range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807. The type-declaration character for Currency is the at sign (@).

The Currency data type is useful for calculations involving money and for fixed-point calculations in which accuracy is particularly important.

So it's not because it's called Currency Data Type that it has actually anything to do with any particular world currency.
What the value you store in a Currency field mean, is up to you.

Formatting

When displaying a Currency Data Type, Access will default to displaying the money symbol for your current locale. Of course, this only makes sense if what you store in that field is money from your own country.

It's probably best to override this behaviour when you design the table so the format will propagate wherever you use the field.
For instance, the highlighted CurrencyRate field is set to use the Currency format, so it will use $ as this is the currency symbol for my locale:

editing the currency field format

Just override that format to format the field into something else.
For instance, as a standard number with 3 decimals, or Euros:

Standard format with 3 decimals enter image description here

When it comes to displaying the data on a form, or in a data sheet, you can also override the control's Format property:

Changing the control's format property

In conclusion

So, the currency symbol is not an issue of what value is stored in the database, but what this value means to your application, and how you display it to your users.

Upvotes: 4

Zaider
Zaider

Reputation: 2013

You can set local computer settings by using the api.

Private Declare Function SetLocaleInfo Lib "kernel32" Alias "SetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String) As Boolean
Private Declare Function GetUserDefaultLCID Lib "kernel32" () As Long 

Public Sub SetCurrencyFormat(format As String)
    Dim lngIdentifier As Long
    lngIdentifier = GetUserDefaultLCID()
    If SetLocaleInfo(lngIdentifier, LOCALE_SCURRENCY, format) = False Then
        MsgBox "Error occured while trying to change the setting."
    End If
End Sub

Call SetCurrencyFormat("$")

Upvotes: 0

Related Questions