Access World Forums

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   Tables (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=7)
-   -   Forcing A Field's Data Type To Be Currency In Dollars ($) Instead Of Regional Setting (https://www.access-programmers.co.uk/forums/showthread.php?t=305823)

Cark 07-11-2019 04:06 AM

Forcing A Field's Data Type To Be Currency In Dollars ($) Instead Of Regional Setting
 
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 07-11-2019 04:17 AM

Re: Forcing A Field's Data Type To Be Currency In Dollars ($) Instead Of Regional Set
 
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 07-11-2019 05:26 AM

Re: Forcing A Field's Data Type To Be Currency In Dollars ($) Instead Of Regional Set
 
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 07-11-2019 10:53 PM

Re: Forcing A Field's Data Type To Be Currency In Dollars ($) Instead Of Regional Set
 
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 07-11-2019 11:02 PM

Re: Forcing A Field's Data Type To Be Currency In Dollars ($) Instead Of Regional Set
 
Do you need to allow for the current exchange rate or will the $ values be fixed?

Cark 07-11-2019 11:03 PM

Re: Forcing A Field's Data Type To Be Currency In Dollars ($) Instead Of Regional Set
 
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 07-12-2019 05:11 AM

Re: Forcing A Field's Data Type To Be Currency In Dollars ($) Instead Of Regional Set
 
Quote:

Originally Posted by Cark (Post 1630458)
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 07-13-2019 07:26 AM

Re: Forcing A Field's Data Type To Be Currency In Dollars ($) Instead Of Regional Set
 
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/...9-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 07-15-2019 04:02 AM

Re: Forcing A Field's Data Type To Be Currency In Dollars ($) Instead Of Regional Set
 
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 07-22-2019 10:03 AM

Re: Forcing A Field's Data Type To Be Currency In Dollars ($) Instead Of Regional Set
 
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.


All times are GMT -8. The time now is 12:31 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World