Forcing A Field's Data Type To Be Currency In Dollars ($) Instead Of Regional Setting (1 Viewer)

Cark

Registered User.
Local time
Today, 15:12
Joined
Dec 13, 2016
Messages
153
I am trying to create a Table with a field which is in US Dollars.

I am building the database on a computer which is set up with GBP Pounds as the default currency in Regional Settings. The database will always be used by computers using GBP Pounds as the default currency (all UK based), however the prices in this particular column need to be stored as Dollars as this is what the majority of costs will be calculated using.

I have done a bit of looking around on google, but could not find how to get around Access changing the currency to whatever is in the regional settings.

Any ideas?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:12
Joined
Oct 29, 2018
Messages
21,358
Hi. This is a good question, and I am not sure there is an answer. I know the Currency data type is recommended for accuracy, but you may have to use a Double instead and just Format it with $ symbols.
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:12
Joined
Jan 23, 2006
Messages
15,364
I don't have an answer specific to your question, but am wondering -since the exchange rate changes GBP Pounds vs USD - it would seem you need to account for same. So, if you accepted data in GBP you could convert to USD via a function and format as DbGuy suggests. Perhaps I'm missing something more basic.
Allen Browne has some info on Currency
 

Cronk

Registered User.
Local time
Tomorrow, 09:12
Joined
Jul 4, 2013
Messages
2,770
You could use
Code:
format(nnn.nn, chr(163) & "#.##")
where nnn.nn is an amount

However, a field set to this will not be updatable.
 

isladogs

MVP / VIP
Local time
Today, 22:12
Joined
Jan 14, 2017
Messages
18,186
Do you need to allow for the current exchange rate or will the $ values be fixed?
 

Cark

Registered User.
Local time
Today, 15:12
Joined
Dec 13, 2016
Messages
153
Just to clarify, all costs will be entered into the database in $, so having data with £s will not be necessary in this particular database.

If I understand the suggestions correctly, I would be best keeping the Cost column in my database as numerical values e.g 2530.52 (which realistically would represent $2530.52) and then use formatting to change these values whenever I use the values in a Form or in an Export?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:12
Joined
Oct 29, 2018
Messages
21,358
Just to clarify, all costs will be entered into the database in $, so having data with £s will not be necessary in this particular database.

If I understand the suggestions correctly, I would be best keeping the Cost column in my database as numerical values e.g 2530.52 (which realistically would represent $2530.52) and then use formatting to change these values whenever I use the values in a Form or in an Export?

Hi Carj. That is correct. However, just be aware of possible rounding errors when you use those numbers in any calculations. Good luck.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:12
Joined
Feb 28, 2001
Messages
27,001
Adding to this discussion on the narrow subject of "Rounding" ... using a DOUBLE for all currency gives you approximately 15 reliable decimal digits and a chance of a 16th digit. Since we are dealing with binary floating-point numbers, decimal fractions don't come out even. However, DOUBLE in monetary computations will give you pretty good fractional stability (i.e. low odds of rounding) until you reach 1,000,000,000,000.00, or 1 trillion in USA nomenclature. For 1 trillion dollars, you have one full decimal "guard" digit remaining, plus the fractional digit. At that level, you have small but non-zero odds of running afoul of some rounding issues. The farther your totals stay from 1 trillion dollars, the more accurate you would be and the less you would worry about bad rounding in computations.

My thought would be to go ahead and use Currency everywhere EXCEPT for presentation purposes. Maybe build a public string function in a general module to convert input currency numbers to output dollar-sign formatted numbers. There will be no rounding for the computations in units of currency. When you use this function, the numeric computation of converting currency to floating-point and then to a formatted string with dollar-signs should be simple only subject to one chance of rounding errors.

The other possibility is to do the opposite of what was suggested here:

https://answers.microsoft.com/en-us...ccess-to/4acae4bd-5bd4-4e88-aa99-86458102682a

There, you are setting the format of something to "Currency" though it wasn't defined that way. Here I would suggest setting the format to something OTHER than currency and then forcing the format to use "$" for output.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 22:12
Joined
Sep 12, 2006
Messages
15,614
I would use currency. It's just a column of numbers. You can format it for display with a dollar symbol, or with no symbol.

The only issue would be if you weren't happy with the accuracy of the pounds/pence, euro/cents, dollar/cents conversions, but you are only talking about fractions of a penny at 4 significant dps.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:12
Joined
Feb 19, 2002
Messages
42,976
Don't confuse the Currency data type with the Currency format. So as Gemma suggested, I too would use the currency datatype and then use a specific format that included the $ sign rather than Currency which would default to the Windows Currency setting.
 

Users who are viewing this thread

Top Bottom