Rounding info needed-A2k

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

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_problem s

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" wrote in message
news:U_pWi.1185$HR6.17@trnddc04...
> 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

Re: Rounding info needed-A2k

On Nov 1, 8:03 pm, "Allen Browne" wrote:
> 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_problem s
>
> 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" wrote in message
>
> news:U_pWi.1185$HR6.17@trnddc04...
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -

FYI, there are rounding issues due to floating point and there are
rounding issues due to a bug in the way subtraction occurs between
numbers where the result is only the decimal portion.

Check out my detailed paper on this subject here:
http://www.fmsinc.com/tpapers/math/index.html

Luke Chung
President
FMS, Inc.
http://www.fmsinc.com

Re: Rounding info needed-A2k

Luke, I'm not sure I understand your point about the subtraction issues.

Your article explains that VBA evaluates:
0.8 - 0.7
correctly as 0.1, but it evalutates:
10000.8 - 10000.7
as 9.99999999985448E-02

My understanding of a floating point number is that the fractional part of
0.8 is the first significant digit, where as the fractional part of 10000.8
is the 6th significant digit. Due to the way floating point numbers work,
the storage won't be anything like the same kind of thing.

I'm not convinced that the example illustrates a problem in the way VBA
executes subtraction, as distinct from a floating point inaccuracy. Am I
missing something? (It is 1am here, so perhaps I'm not thinking clearly.)

--
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.

"FMS Development Team" wrote in message
news:1194011760.632681.113130@o38g2000hse.googlegroups.com.. .
>
> FYI, there are rounding issues due to floating point and there are
> rounding issues due to a bug in the way subtraction occurs between
> numbers where the result is only the decimal portion.
>
> Check out my detailed paper on this subject here:
> http://www.fmsinc.com/tpapers/math/index.html
>
> Luke Chung
> President
> FMS, Inc.
> http://www.fmsinc.com

Re: Rounding info needed-A2k

On Nov 2, 12:07 pm, "Allen Browne" wrote:
> Luke, I'm not sure I understand your point about the subtraction issues.
>
> Your article explains that VBA evaluates:
> 0.8 - 0.7
> correctly as 0.1, but it evalutates:
> 10000.8 - 10000.7
> as 9.99999999985448E-02
>
> My understanding of a floating point number is that the fractional part of
> 0.8 is the first significant digit, where as the fractional part of 10000.8
> is the 6th significant digit. Due to the way floating point numbers work,
> the storage won't be anything like the same kind of thing.
>
> I'm not convinced that the example illustrates a problem in the way VBA
> executes subtraction, as distinct from a floating point inaccuracy. Am I
> missing something? (It is 1am here, so perhaps I'm not thinking clearly.)
>
> --
> 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.
>
> "FMS Development Team" wrote in messagenews:1194011760.632681.113130@o38g2000hse.googlegroup s.com...
>
>
>
>
>
> > FYI, there are rounding issues due to floating point and there are
> > rounding issues due to a bug in the way subtraction occurs between
> > numbers where the result is only the decimal portion.
>
> > Check out my detailed paper on this subject here:
> >http://www.fmsinc.com/tpapers/math/index.html
>
> > Luke Chung
> > President
> > FMS, Inc.
> >http://www.fmsinc.com- Hide quoted text -
>
> - Show quoted text -

Hi Allen,

> Your article explains that VBA evaluates:
> 0.8 - 0.7
> correctly as 0.1, but it evalutates:
> 10000.8 - 10000.7
> as 9.99999999985448E-02

The point is that both should evaluate to 0.1 which Access/Windows has
no problem representing exactly. The difference is due to the
subtraction bug when integers are lost.

So if we change it to 10001.8 - 10000.7, the answer becomes 1.1
exactly, which matches 1.8 - 0.7.

Does that help?

Luke

Re: Rounding info needed-A2k

Thanks for the reply, Luke.

I still suspect conceive of this example as being a floating point accuracy
issue, but I appreciate your reply.

All the best

--
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.

"FMS Development Team" wrote in message
news:1194027627.973839.120620@d55g2000hsg.googlegroups.com.. .
> On Nov 2, 12:07 pm, "Allen Browne" wrote:
>> Luke, I'm not sure I understand your point about the subtraction issues.
>>
>> Your article explains that VBA evaluates:
>> 0.8 - 0.7
>> correctly as 0.1, but it evalutates:
>> 10000.8 - 10000.7
>> as 9.99999999985448E-02
>>
>> My understanding of a floating point number is that the fractional part
>> of
>> 0.8 is the first significant digit, where as the fractional part of
>> 10000.8
>> is the 6th significant digit. Due to the way floating point numbers work,
>> the storage won't be anything like the same kind of thing.
>>
>> I'm not convinced that the example illustrates a problem in the way VBA
>> executes subtraction, as distinct from a floating point inaccuracy. Am I
>> missing something? (It is 1am here, so perhaps I'm not thinking clearly.)
>>
>> "FMS Development Team" wrote in
>> messagenews:1194011760.632681.113130@o38g2000hse.googlegroup s.com...
>>
>>
>>
>>
>>
>> > FYI, there are rounding issues due to floating point and there are
>> > rounding issues due to a bug in the way subtraction occurs between
>> > numbers where the result is only the decimal portion.
>>
>> > Check out my detailed paper on this subject here:
>> >http://www.fmsinc.com/tpapers/math/index.html
>>
>> > Luke Chung
>> > President
>> > FMS, Inc.
>> >http://www.fmsinc.com- Hide quoted text -
>>
>> - Show quoted text -
>
> Hi Allen,
>
>> Your article explains that VBA evaluates:
>> 0.8 - 0.7
>> correctly as 0.1, but it evalutates:
>> 10000.8 - 10000.7
>> as 9.99999999985448E-02
>
> The point is that both should evaluate to 0.1 which Access/Windows has
> no problem representing exactly. The difference is due to the
> subtraction bug when integers are lost.
>
> So if we change it to 10001.8 - 10000.7, the answer becomes 1.1
> exactly, which matches 1.8 - 0.7.
>
> Does that help?
>
> Luke

Re: Rounding info needed-A2k

Okay, I'm still in the dark. I've read your response and KIND of
understand. The link about 'accuracy problems' lost me at my click of it.
::grin:: (math major, I'm not!)

And, I've read the discussion between you and FMS.

However, I've still got a problem, as I see it.

I've got an operator who enters a payment to an account of 1353.57. That
amount is stored in a Number field with a Field Size of Double. Decimal
Places = Auto. It's not divided by anything...just entered as 1353.57 and
my program multiplies by -1 so it's a negative number and on to the next
payment. But, instead of saving the entered amount -1353.57, it's stores
something like -1353.569999999999999988832982929828 (etc).
Or, -1353.570000000000000000000002737387237732 (etc).

Who decided it should be something other than -1353.57? The data type? Or,
did my multiplying the input number (a positive number) by -1 do the nasty
deed of making it a minus 1353.5699999999999(etc)?

And how do I get it stored as -1353.57 (exactly as input rather than this
bazaar desire of whatever to be so accurate I could use it to build a DNA
example)?

I'll NEVER need a field saved that's the result of dividing something by
something else (ex 1353.57 / 9 = 150.841111282817171761 or whatever).
Perhaps in a report I'd do something like that, but I'd not need to save it
anywhere (as far as I know). And, if I do it in a report or whatnot, I can
see doing the round() function now that I know of this bazaar desire to be
accurate out to 82 million digits (or, whatever). But, all I'm trying to do
is store an accurate negative amount (accurate to me is what was input) that
is, in fact, dollars and cents.

I chose the number/double type over the currency because I didn't like the
dollar signs appearing all over the place. Perhaps that was my error? I
now realize I could have used the currency type and then standard format, or
whatever. But, it's a done deal...unless I change the type, which I guess I
could do. But, is that necessary to get what I'm trying to get?

Second to the last paragraph of your response seems to indicate I'll still
need to do the round() function prior to saving the data input to the
approrpiate field. Where do I do that?

Thanks much for all the info...and sorry I'm seeming so dense (perhaps I
am...).
ron

"Allen Browne" wrote in message
news:472a6954$0$17164$5a62ac22@per-qv1-newsreader-01.iinet.n et.au...
> 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_problem s
>
> 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.
>

Re: Rounding info needed-A2k

"Ron" wrote

> Okay, I'm still in the dark. I've read your response and
> KIND of understand. The link about 'accuracy problems'
> lost me at my click of it.

> Thanks much for all the info...and sorry I'm seeming so
> dense (perhaps I am...).

Dense isn't an accurate description. It's just in understanding how
computers store the numbers, internally.

We think in decimal... where each digit position represents a power of ten,
to the left of the decimal point, and a negative power of ten (inverse), to
the right... But, in the computer, in floating point, the numbers are
represented in powers of two, positive to the left of the binary point and
negative to the right.

Most numbers cannot be translated _exactly_ from decimal to binary, and it
is the calculations from the decimal that you enter, to the binary that is
stored which results in the variation. It is not feasible to compensate,
and the calculations do not automatically compensate, when you calculate
from the binary back to decimal in order to display it to you.

That may still sound like Greek to an English speaker (perhaps more
accurately it sounds like "geek"). I was fortunate, in my early days in the
computer business, to have texts and instructors much more talented at
explaining this than I am, but even they needed more than a single newsgroup
post to educate us on the topic.

Larry Linson
Microsoft Access MVP