X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Use Of Currency As Data Type, Access 2016    
post Apr 14 2018, 12:04 PM

Posts: 434
Joined: 12-October 03
From: Texas

I was reading a thread a few days ago and as a "side comment" in one of the replies, someone stated that use of currency data type was recommended any time that no more than 4 decimal places of accuracy were needed. I can't find the thread now, but I think it was Bruce M. I did a search this morning and found several threads on the topic, all recommending currency as the data type for fields which require fractions even if they are not truly representing currency. Before I go through and change the data types in my tables, I just would to make sure I am doing the right thing.

In my application I am storing laboratory analytical results, and never do I need more than 3 decimal places of accuracy. I should change from Single to Currency?

If I choose Currency as the data type and 2 as the Decimal Places, this means that four decimal digits are stored, but only two are displayed? And if I choose 2 Decimal places in the table design, all forms and reports will default to displaying two decimals? but if I choose to format a text box differently, I can format the text box to show anywhere from 0 to 4 decimal places?

The $ sign shows up in the data view of the table data. Is the $ symbol actually stored in the table? In my forms and reports, will I have to set a format property to prevent the $ symbol from showing?
Go to the top of the page
post Apr 14 2018, 12:40 PM

UtterAccess VIP
Posts: 5,982
Joined: 30-June 11

See if http://www.kallal.ca/Articles/rounding/Rounding.html doesn't help answer this question for you.

Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc

All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
post Apr 14 2018, 02:57 PM

Posts: 434
Joined: 12-October 03
From: Texas

I read the article and have a question. So the Currency type is a "scaled integer that stores the number and the decimal place". Does it always store four as the decimal place locator, and the decimal places property in the table only affects the default of how many decimal places are displayed in a text box etc?

Or does the Decimal Places property for a currency data type actually control how many decimal places are stored in the record?

Also, it looks like Currency data types take 8 bytes whereas Single only requires 4 bytes. How important is this?
Go to the top of the page
post Apr 14 2018, 03:20 PM

UtterAccess VIP
Posts: 9,985
Joined: 10-February 04
From: South Charleston, WV

The main drawback to Single is that fractional parts produced by arithmetic calculations aren't always entirely correct. This is because the decimal numbers which are input are converted into an internal representation that is based on 16 not 10. It only ever affects calculations which result in numbers with fractional portions and in those cases only slightly if at all. This format was, in fact, designed for scientific purposes such as yours, but no scientist would prefer numbers that are even slightly wrong over numbers that are absolutely right, the latter being what the packed decimal notation, as it is generally known, offers, along with a number length of up to 31 digits. In Access, as far as I know, the packed decimal notation is used for both decimal and currency data types for table fields and which are essentially the same thing. Unfortunately, in Access VBA, there is no decimal format, just currency. I think this is why they advise using currency in the table field definition. Personally, I use currency for money and I use decimal for everything else containing fractions. Long integer is used for indexes and counters less than 2,100,000,000 and short integer for indexes and counters less than 32,000.

Single and double also use more computer time, but you're talking fractions of a second per calc.

I don't know that I would convert if you're happy with the way it's working.

Robert Crouser

My company's website
Go to the top of the page
post Apr 14 2018, 04:35 PM

UtterAccess VIP
Posts: 2,657
Joined: 12-April 07
From: Edmonton, Alberta Canada

No big deal in regards to disk storage. I mean, unless you storing millions of records AND you are tight for space – a non-issue these days.

The currency type for all discussion can be assumed to always and ONLY store the 4 values after the decimal point (so yes, it always stores the 4 values).

At the end of the day, the currency type choice is really important for accounting values, since you not get rounding errors.

And I often do use currency if I need a number that only going to have say 1 or 2 decimal points. And of course the $ is not stored in the table. So like a date has an “internal” value, you are free to display that date value in any format you like, and it not related to “internal” value. You can even display date values as text like “Saturday April 14, 2014”. So you not saving “Saturday” in the table.

Same goes for currency – the “$” only appears as a result of formatting for external view – internal it always the number + 4 decimal place numbers.

You can disable the default “$” on forms, reports etc. if you set the formatting for the given control.

In your case, it likely a non-issue. However, if adding up values and having some rounding issues for you given scenario, then currency still a good choice.

I don’t think I would go to all the trouble to change a whole bunch of existing columns in a table until such time you are being bitten by some rounding issues.

Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
post Apr 14 2018, 08:49 PM

Posts: 434
Joined: 12-October 03
From: Texas

thank you all, I have a much better understanding now.

Looks like for my situation, it is the proverbial much ado about nothing. Not sure exactly what I will do now. There are only a few table fields impacted, so I may go ahead and change just to prevent any potential rounding issues.

Again, thank you for taking the time to explain the bases for your own decisions.
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    20th August 2018 - 08:52 PM