Re: Rounding info needed-A2k

There are a couple of factors that contribute to this issue, Ron.

The first is the field type. If you use a Number field (size Double or Single), you have a floating point data type. This is really useful for handling enormous numbers (+/- numbers with millions of billions) or miniscule numbers (hundreds of zeros after the decimal point before the digits really start), but they are limited to around 15 significant digits (for a Double.) Most fractional numbers cannot be stored precisely in this binary form, just as many numbers (such as 1/7 or 1/3) cannot be stored precisely in the decimal form without an infinite number of digits.

Consequently, floating point numbers always have these rounding problems. For more details, see:
http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems

One solution is to use a fixed point data type instead of a floating point. The Currency type in Access does this. It stores the number as if it were an 8-byte integer, and then adds the decimal point 4-digits from the right. This arrangement means that the 4 digits to the right of the decimal point are precise. The disadvantage is that it is not suitable for numbers where more than 4 digits are required, or for really enormous numbers. This is ideal for most currencies. It is also useful for other scenarios (e.g. to store precise distances in metres, with a resolution down to 1/10 mm.)

Your example wanted only 2 decimal places. The Currency format typically shows 2, though it stores 4. Therefore you will need to use Round() when performing calcuations that write a value so the Currency field, so ensure the last 2 digits are stored as zeros. If you want to display the currency field as fixed to 2 decimal places (not dollars and cents), set its Format property to Fixed, and the Decimal Places to 2.

There is another data type in Access: a Number field of size Decimal. This is a scalar data type. It is similar to the fixed point, but you can specify where the decimal point goes for any particular field (unlike the Currency field, where it is always 4 digits.) While this is a great concept, unfortunately the implementation in Access is buggy, so I can't recommend using it. Here's one example of why:
http://allenbrowne.com/bug-08.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ron" <ronSPAMBLOCKINGwest777@xxxxxxxxxxx> wrote in message
news:U_pWi.1185$HR6.17@xxxxxxxxxxx
Hi All,

I've noticed that dollar figures entered as 13.46 are going into my tables as 13.459999997654399 (999 etc added by me here, but it's close). Or, as 13.460000005435 rather than 13.46 only. So, over time, I could see how this will add or subtract a penny or 2 to an aging report that needs to be more accurate than that.

I also have a control on a form that is either visible or not based on comparing a simple check amount entry of 3145.54 (one control on a payment form) to a sum of the payments, all entered as individual dollar, cents entries that eventually SHOULD total the check amount. But, unfortunate, they aren't totalling the check amount--after all the payments have been recorded, the sum of the payments equals something like 3145.5400000024282 or some such. Hello?

So, questions...

Have I designed my Amounts/Quantities fields wrong and that's why they're allowing these unrounded amounts? If so, how should they be designed to avoid this?

Is there a way to correct this inclination of Access' to record amounts not as entered on data already entered and on any future entries?

I've kinda solved my *unequal* problem above by using the round() function, but is that the best way? The fact that the entries are going in as unrounded numbers bothers me. Should it? Shouldn't this rounding thing be done at the table/form level when input is done thereby not requireing me to "round" it all since it should be rounded in the first place?

Can you point me somewhere to learn about this rounding problem?

TIA
ron

.

Relevant Pages

  • Re: Rounding info needed-A2k
    ... But, instead of saving the entered amount -1353.57, it's stores ... accurate out to 82 million digits ... I chose the number/double type over the currency because I didn't like the ... you have a floating point data type. ...
    (comp.databases.ms-access)
  • Re: 15 Significant Digits Limitation a Mistake for Spatial Informa
    ... DP does not restrict to 15 decimal digits. ... Input and output precision are more tightly linked in Excel ... Decimal data type or roll your own extended precision data types. ...
    (microsoft.public.excel)
  • Re: Access 10 (64bit) -Integer & Table size
    ... Access03 that I currently use can go up to 10 digits (32 bit ... it can handle up to 19 digits as per above. ... However, this will NOT help you, since in a table, the long data type HAS NOT changed, and remains a 32 bit long. ... So a typical mailing address is about 120 bytes (characters) in size ...
    (comp.databases.ms-access)
  • Re: Working with large numbers
    ... > I am writing an application the must use large numbers(16-20 digits). ... > I am using double data type but vb by default converts the following ... Dim Value1 As Variant ... if a Variant variable is assigned a value that was cast ...
    (microsoft.public.vb.general.discussion)
  • Re: Alternate solution to krx113
    ... when I was experiementing and creating the code, and never got rid of them. ... formatting printfwith a number before it, it will right justify it by that number MINUS the amount of digits %d contains. ... printf(" *");} else { ...
    (comp.lang.c)