UtterAccess.com
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
> Summing Fields Problems, Access 2010    
 
   
AlBilly
post Feb 16 2017, 09:41 AM
Post#1



Posts: 91
Joined: 23-September 16



Hi,

I just encounter a problem with a query, I am summing 5 fields to get a total but I am getting a very big number and I simply don't get it at all
0+0+0+.01+0=9,99999977648258E-03
0+0+0+-.01+0=-9,99999977648258E-03

How can this be possible ?

The data type of these fields are "réél simple" I guess it is double in English system but not sure or it could be real single
Attached File(s)
Attached File  Doc1.zip ( 26.56K )Number of downloads: 4
 
Go to the top of the page
 
GroverParkGeorge
post Feb 16 2017, 10:02 AM
Post#2


UA Admin
Posts: 30,177
Joined: 20-June 02
From: Newcastle, WA


Actually, that appears to be appropriate.

Note that the exponent is -03, i.e. minus 03, which means that this is a number less than 0. Much less.

I'm not up on the fine details of scientific notation, but I'd say the results are .00999999977648258 and - .0099999997764825

And that would be predictable since your source numbers include .01 and -.01, which are the values to which these two results would round.

In other words, it's a combination of how doubles are stored and the rounding applied.

I'm sure you'll get additional input on the details, but that's the high level explanation of what you are seeing.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Feb 16 2017, 10:12 AM
Post#3


UA Admin
Posts: 30,177
Joined: 20-June 02
From: Newcastle, WA


Wikipedia seems to have a pretty good explanation.

--------------------
Go to the top of the page
 
AlBilly
post Feb 16 2017, 10:38 AM
Post#4



Posts: 91
Joined: 23-September 16



on some other data rows, the same numbers .01 ends up with something either E-12 even E-15 very weird.


Thanks for the information
Go to the top of the page
 
GroverParkGeorge
post Feb 16 2017, 10:52 AM
Post#5


UA Admin
Posts: 30,177
Joined: 20-June 02
From: Newcastle, WA


Not weird at all.

I recommend a study session on how doubles are stored and used. It's fascinating, albeit obscure for much of what we do. In this case, it's pretty clear that your results are being impacted by the datatype of the source values and how they are stored and rounded.

--------------------
Go to the top of the page
 
projecttoday
post Feb 16 2017, 10:58 AM
Post#6


UtterAccess VIP
Posts: 8,285
Joined: 10-February 04
From: South Charleston, WV


You would probably be better off avoiding the problem entirely: don't use the single/double data types. Use decimal instead. That way .01 is always .01.

--------------------
Robert Crouser
Go to the top of the page
 
GroverParkGeorge
post Feb 16 2017, 12:29 PM
Post#7


UA Admin
Posts: 30,177
Joined: 20-June 02
From: Newcastle, WA


I'd probably recommend that, but since we have no insight into how these values are generated, it's hard to know whether the current values are appropriate or not in the first place.

--------------------
Go to the top of the page
 
AlBilly
post Feb 16 2017, 12:50 PM
Post#8



Posts: 91
Joined: 23-September 16



interesting article indeed, did not know about this, still much to learn..

Regards
Go to the top of the page
 
projecttoday
post Feb 16 2017, 01:00 PM
Post#9


UtterAccess VIP
Posts: 8,285
Joined: 10-February 04
From: South Charleston, WV


So, AlBilly, do you have a specific reason for using double? In my experience it is rare. Most people do it because they don't know you need to set the scale property to get decimals to work.

--------------------
Robert Crouser
Go to the top of the page
 
AlBilly
post Feb 16 2017, 03:52 PM
Post#10



Posts: 91
Joined: 23-September 16



The database was set like this since 2008, I just pick it up as it need an upgrade.
What I did is create new data tables with decimal as data type and ran queries to transfer data rounded as it should be , the results are the same so problem solve on queries and reports
This post has been edited by AlBilly: Feb 16 2017, 03:52 PM
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th August 2017 - 02:26 AM