Why does access do this?

S

S.Hoitinga

Hi all,

I am really amazed about the way Access hadeles floating numbers.
F.I. when in a query i multiply fields with values like 3,7 and 2 I get a
result that looks like 3.3999000137
or something close.
I have checked the values as being the real numbers I have shown you. There
is nothing else, but Acces comes with some really strange results.
I know how to workaround the roblem but find this hard to explain to pupils.
Anyone have any suggestions?

Mostly Obliged,

Syb
 
M

Mike Labosh

I am really amazed about the way Access hadeles floating numbers.
F.I. when in a query i multiply fields with values like 3,7 and 2 I get a
result that looks like 3.3999000137
or something close.
Click to expand...

Yeah, VB 3, 4, 5, 6 and VBA are all infected with it too. I never use
single or double as a data type. If I need a fractional value, Currency is
what you want, because internally it's stored as a dual-value scaled
integer, so you still get the precision that you need.


--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS!"
~~ General Barringer ~~
 
S

S.Hoitinga

Thanks for telling me Frank,

I only question: why haven't the access crew coped with this weird problem?
In Excel a multiplication of 2 and 3.7 leads to 7.4 and nothing more or
less.
Regardless of the formatting, as it should be.

In access it seems to be a whole different story,

Greets,

Syb
 
T

Tom Wickerath

Mike and Syb:

I cannot reproduce this observation on my PC, using Access 2002 with all of the latest updates. I
created a new table named tblMultiplicationTests. There are four fields in this table (no primary
key for this quick test):

SngNum1 Number / Single
SngNum2 Number / Single
DblNum1 Number / Double
DblNum2 Number / Double


I created a single record in this table:

SngNum1 SngNum2 DblNum1 DblNum2
3.7 2 3.7 2


and then I created a query to test the results. Here is the SQL statement:

SELECT [SngNum1]*[SngNum2] AS SngResult,
[DblNum1]*[DblNum2] AS DblResult
FROM tblMultiplicationTests;


Here are the results of running this query:

SngResult DblResult
7.40000009537 7.4


Tom
___________________________________

I am really amazed about the way Access hadeles floating numbers.
F.I. when in a query i multiply fields with values like 3,7 and 2 I get a
result that looks like 3.3999000137
or something close.
Click to expand...

Yeah, VB 3, 4, 5, 6 and VBA are all infected with it too. I never use
single or double as a data type. If I need a fractional value, Currency is
what you want, because internally it's stored as a dual-value scaled
integer, so you still get the precision that you need.


--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS!"
~~ General Barringer ~~
 
R

Rosco

Mike, Syb, Tom:

I just duplicated Tom's experiment in A2K SP3 all updates
and got the same results.

Rosco

Tom Wickerath said:
Mike and Syb:

I cannot reproduce this observation on my PC, using Access 2002 with all of the latest updates. I
created a new table named tblMultiplicationTests. There are four fields in this table (no primary
key for this quick test):

SngNum1 Number / Single
SngNum2 Number / Single
DblNum1 Number / Double
DblNum2 Number / Double


I created a single record in this table:

SngNum1 SngNum2 DblNum1 DblNum2
3.7 2 3.7 2


and then I created a query to test the results. Here is the SQL statement:

SELECT [SngNum1]*[SngNum2] AS SngResult,
[DblNum1]*[DblNum2] AS DblResult
FROM tblMultiplicationTests;


Here are the results of running this query:

SngResult DblResult
7.40000009537 7.4


Tom
___________________________________

I am really amazed about the way Access hadeles floating numbers.
F.I. when in a query i multiply fields with values like 3,7 and 2 I get a
result that looks like 3.3999000137
or something close.
Click to expand...

Yeah, VB 3, 4, 5, 6 and VBA are all infected with it too. I never use
single or double as a data type. If I need a fractional value, Currency is
what you want, because internally it's stored as a dual-value scaled
integer, so you still get the precision that you need.


--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS!"
~~ General Barringer ~~
Click to expand...
 
M

Mike Labosh

Access 2000 + SP3 & all patches:

Private Sub testSingle()

Dim x As Single, y As Single

For x = 0 To 10 Step 0.1

For y = 0 To 10 Step 0.1
Debug.Print x * y
Next y

Next x

End Sub

Private Sub testDouble()

Dim x As Double, y As Double

For x = 0 To 10 Step 0.1

For y = 0 To 10 Step 0.1
Debug.Print x * y
Next y

Next x

End Sub

I get stuff like this:
2.96999999999999 'Should be 2.97
3.95999999999999 'Should be 3.96
4.94999999999999 'Etc.
5.93999999999999
6.92999999999999
7.91999999999998
8.90999999999998
9.89999999999998

--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS!"
~~ General Barringer ~~
 
A

Albert D. Kallal

S.Hoitinga said:
Thanks for telling me Frank,

I only question: why haven't the access crew coped with this weird
problem?
In Excel a multiplication of 2 and 3.7 leads to 7.4 and nothing more or
less.
Regardless of the formatting, as it should be.

In access it seems to be a whole different story,
Click to expand...

Actually, the different is you are now using a software development
tool..and not really a "end user" tool. It is a defense between going to the
store to purchase some Aspirin..and then learning the biology and medial
reasons behind why your throat gets sore when you are sick. (increased blood
flow as your body fights the infection causes the throat to swell..and, a
few other reasons). My point is that when you migrate from Excel to
ms-access, you are taking the next leap in terms of learning about software.

So, this is a issue of computers, and how numbers are resented.

"real" values in a computer are actually a appromaxaton. If you use integer
values
(or so called whole values), then a computer can do a much better job.

integer, long, and the currency data types in ms-access are actually stored
as whole numbers, and thus not subject to rounding...or approximate errors.
If you are doing any financial business software...you better learn this
lesson in a hurry. And, if you don't want to learn this lesson, then you
will have to leave software development to professionals.

What developers do is normally "hide" this complexity. And, your pocket
calculator, or Excel uses whole numbers..and shifts the results to reduce
the rounding errors.
In Excel a multiplication of 2 and 3.7 leads to 7.4 and nothing more or
less.
Click to expand...

Actually, Excel has the same problem is you use the wrong data type.

While in excel, do the following, and jump to the VBA editor:

alt-f11

ctrl-g

Your cursor is now in the debug window for Excel, type in:

? ( (2.01 + .01) * 2 ) = 4.04

You will see that the above gives the answer of false. And, even if you are
NOT multiply numbers, try the following:

? ( (2.01 + .01) ) = 2.02

Again, the above suffers from serous rounding errors, and the above
expression actually returns false. The above is certainly not a complex
calculation...and yet we see that the above expression:

? ( (2.01 + .01) ) = 2.02
(2.02) = 2.02
2.02 = 2.02

And, yet..we get false!!!

So, excel most certainly can, and does suffer from rounding. This is a issue
of how computers work, and as a software developer we deal with this problem
every day....
 
I

Immanuel Sibero

It's not a weird problem. We humans think of numbers in base10 (i.e. 0
through 9). Floating numbers are fractions of whole numbers.
Now, we all know that computers process in binary (i.e. base2 or 0 through
1). The problem is there is no way to accurately represent or convert
floating numbers to binary. So any representations of floating numbers in
binary are in fact approximations.

HTH,
Immanuel Sibero
 
T

Tom Wickerath

Hi Mike,

Your results are in line with what I would expect:
I get stuff like this:
2.96999999999999 'Should be 2.97
3.95999999999999 'Should be 3.96
Click to expand...

In other words, your results are pretty darn close to the expected result. The original poster
stated the following:

".... am really amazed about the way Access hadeles floating
numbers. F.I. when in a query i multiply fields with values like 3,7
and 2 I get a result that looks like 3.3999000137 or something close."

I took the "F.I." to mean "For instance". Note to Syb: Avoid using abbreviations that other's
may not understand. Also, it appears as if Syb's decimal separator is set as a comma. Thus, I'm
assuming that 3,7 is the same as 3.7. There also appears to be a missing period after the 2 in
this part of Syb's statement: "...with values like 3,7 and 2 I get...". I took this to be the
end of one sentence and the beginning of another, as in:
"...with values like 3,7 and 2. I get..."

Clearly, 3.3999000137 is no where close to 7.4. These results are not considered to be
approximations of each other.

Tom

PS. Heading off to work now, so I won't be able to respond during the next 10 hours or so.

_____________________________

Access 2000 + SP3 & all patches:

Private Sub testSingle()

Dim x As Single, y As Single

For x = 0 To 10 Step 0.1

For y = 0 To 10 Step 0.1
Debug.Print x * y
Next y

Next x

End Sub

Private Sub testDouble()

Dim x As Double, y As Double

For x = 0 To 10 Step 0.1

For y = 0 To 10 Step 0.1
Debug.Print x * y
Next y

Next x

End Sub

I get stuff like this:
2.96999999999999 'Should be 2.97
3.95999999999999 'Should be 3.96
4.94999999999999 'Etc.
5.93999999999999
6.92999999999999
7.91999999999998
8.90999999999998
9.89999999999998

--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS!"
~~ General Barringer ~~
 
Top