Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-11-2019, 04:06 AM   #1
Cark
Newly Registered User
 
Join Date: Dec 2016
Posts: 109
Thanks: 50
Thanked 1 Time in 1 Post
Cark is on a distinguished road
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?

Cark is offline   Reply With Quote
Old 07-11-2019, 04:17 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,036
Thanks: 45
Thanked 953 Times in 935 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Tera (07-15-2019)
Old 07-11-2019, 05:26 AM   #3
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,060
Thanks: 79
Thanked 2,003 Times in 1,951 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
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

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 07-11-2019, 10:53 PM   #4
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,131
Thanks: 3
Thanked 463 Times in 456 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
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.
Cronk is offline   Reply With Quote
Old 07-11-2019, 11:02 PM   #5
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,055
Thanks: 110
Thanked 2,727 Times in 2,490 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
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?
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 07-11-2019, 11:03 PM   #6
Cark
Newly Registered User
 
Join Date: Dec 2016
Posts: 109
Thanks: 50
Thanked 1 Time in 1 Post
Cark is on a distinguished road
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?
Cark is offline   Reply With Quote
Old 07-12-2019, 05:11 AM   #7
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,036
Thanks: 45
Thanked 953 Times in 935 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Forcing A Field's Data Type To Be Currency In Dollars ($) Instead Of Regional Set

Quote:
Originally Posted by Cark View Post
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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 07-13-2019, 07:26 AM   #8
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,219
Thanks: 86
Thanked 1,620 Times in 1,503 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 07-15-2019, 04:02 AM   #9
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,769
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
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.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 07-22-2019, 10:03 AM   #10
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,930
Thanks: 13
Thanked 1,527 Times in 1,453 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Currency Rounding for Retail Dollars $$$.x9 Rhino999 Forms 16 12-20-2013 01:54 PM
Desktop services template changing currency to pounds from dollars Day101 General 6 11-17-2013 12:34 PM
Setting form value as £0.00 on Lost focus - not working for currency data type sian_hug Modules & VBA 8 06-14-2013 03:41 PM
Currency control won't show dollars davidbodhi Forms 4 11-08-2005 09:53 AM
Forcing the field type st3ve Modules & VBA 0 01-29-2002 03:11 PM




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


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


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