Arithmetic with "Double"

[Bill]

If you can follow the output lines below in
recognizing the two values that follow the
date, notice that the last number in the line
is the accumulation or running balance of
those two values. In particular, notice the
last two lines where +408.91 -408.91
should algebraically yield zero (0).

So what gives with the scientific notation
-1.70530256582424E-13?

All of the numeric variables are DIM'd
Double. I don't see anywhere in the HELP
text where one can set the decimal places
to be used in the calculations.

"F01-368317.QIF 07/29/2000 0 0 Opening Balance 0"
"F01-368317.QIF 07/29/2000 0 760 Directed Transfers 760"
"F01-368317.QIF 03/26/2001 0 496.64 Directed Transfers 1256.64"
"F01-368317.QIF 07/28/2001 -760 0 Directed Transfers 496.64"
"F01-368317.QIF 09/10/2001 0 6623 Roster Receipts (37@ 179.00) 7119.64"
"F01-368317.QIF 09/10/2001 0 179 Roster Receipts (1@ 179.00) 7298.64"
"F01-368317.QIF 09/10/2001 -19.29 0 Postage And Envelopes 7279.35"
"F01-368317.QIF 09/10/2001 -550.44 0 Coffee,Water (35 gals @ 14.75/gal+tax)
6728.91"
"F01-368317.QIF 09/10/2001 -6224 0 Rooming Charges 504.91"
"F01-368317.QIF 09/10/2001 -96 0 Energy Surcharge (1.26/night/person)
408.91"
"F01-368317.QIF 09/10/2001 -408.91 0 Directed
Transfers -1.70530256582424E-13"

 

[John W. Vinson]

If you can follow the output lines below in
recognizing the two values that follow the
date, notice that the last number in the line
is the accumulation or running balance of
those two values. In particular, notice the
last two lines where +408.91 -408.91
should algebraically yield zero (0).

So what gives with the scientific notation
-1.70530256582424E-13?

All of the numeric variables are DIM'd
Double. I don't see anywhere in the HELP
text where one can set the decimal places
to be used in the calculations.

"F01-368317.QIF 07/29/2000 0 0 Opening Balance 0"
"F01-368317.QIF 07/29/2000 0 760 Directed Transfers 760"
"F01-368317.QIF 03/26/2001 0 496.64 Directed Transfers 1256.64"
"F01-368317.QIF 07/28/2001 -760 0 Directed Transfers 496.64"
"F01-368317.QIF 09/10/2001 0 6623 Roster Receipts (37@ 179.00) 7119.64"
"F01-368317.QIF 09/10/2001 0 179 Roster Receipts (1@ 179.00) 7298.64"
"F01-368317.QIF 09/10/2001 -19.29 0 Postage And Envelopes 7279.35"
"F01-368317.QIF 09/10/2001 -550.44 0 Coffee,Water (35 gals @ 14.75/gal+tax)
6728.91"
"F01-368317.QIF 09/10/2001 -6224 0 Rooming Charges 504.91"
"F01-368317.QIF 09/10/2001 -96 0 Energy Surcharge (1.26/night/person)
408.91"
"F01-368317.QIF 09/10/2001 -408.91 0 Directed
Transfers -1.70530256582424E-13"

A Double field is an approximation: a 48-bit mantissa multiplied by two to the
power of a 16-bit exponent. This gives a range from 10^-308 to 10^309 (or
thereabouts) with about 14 decimal places of precision.

Just as the fraction 1/7 cannot be represented exactly as a decimal fraction -
it's 0.142856142856142856 repeating endlessly - so 0.1 cannot be represented
exactly as a binary fraction. Any Floating Point number will have this kind of
roundoff error (and it's not Access, you get the same problem in Fortran II,
PL/I, COBOL, C or any other language).

Since it appears that you're dealing with money here, consider using a
Currency datatype (not to be confused with a currency FORMAT - Currency is not
even one of the Number datatypes, it's separate). A Currency value is a 64-bit
scaled integer with exactly four, no more no fewer, decimal places and NO
roundoff error. Alternatively, you can use a Number... Decimal datatype, which
lets you specify both the precision and the scale.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bill]

Thanks John.

I was aware of the nature of floating-point internal
representation and such things that occur with 1/7
and such. I just had the idea that simple additions
and subtractions wouldn't cause any numeric
anomalies. Anyway, what now surprises me here
is that it didn't occur to me to investigate the use
of "currency" in VBA, as it's something I use in
EXCEL on a regular basis.

Be careful John as you age, as this kind of stuff
seems to be creeping in on me.

Thanks again,
Bill

 

[John W. Vinson]

Be careful John as you age, as this kind of stuff
seems to be creeping in on me.

hey youngster... don't tell your elders what they already know! <sigh>

(going to check my Medicare status)
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com