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
> Access - Math Giving Strange Results, Access 2007    
 
   
t.heisler
post May 28 2020, 02:34 PM
Post#1



Posts: 20
Joined: 28-January 19



Hello there,

Just noticed something strange about access as far as the logic behind mathematical operations.

Can someone explain to me why access calculates this in the immediate window ?

?50.60-49.95
0.649999999999999

I'm expecting 0.65, I guess I'm stuck with converting everything to 2 decimal places before doing other calculations.

I have a database that is used to calculate measurements for calibration software, so this is a little annoying.

Thanks
Go to the top of the page
 
GroverParkGeorge
post May 28 2020, 02:37 PM
Post#2


UA Admin
Posts: 37,510
Joined: 20-June 02
From: Newcastle, WA


This is a known problem with certain number types.

Depending on what you are calculating, there may be alternatives that are more reliable. What degree of precision is required?
This post has been edited by GroverParkGeorge: May 28 2020, 02:39 PM

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post May 28 2020, 02:38 PM
Post#3


UA Admin
Posts: 37,510
Joined: 20-June 02
From: Newcastle, WA


This may be a better explanation.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
t.heisler
post May 28 2020, 02:51 PM
Post#4



Posts: 20
Joined: 28-January 19



I only need up to 4 decimal places.

?Format((50.60-49.96),"0.0")
0.6
?Format((0.65),"0.0")
0.7

I have a query doing this calculation using two currency fields that store the 50.60 and 49.96.

The query returns 0.7 when the user expects 0.6.

If I convert both to doubles first it gives the expected result, I just didnt realize this was a thing until now. (Been working with access for 7 years)


Format(CDbl([UUTAsFound])-CDbl([StandardValue]),[ResEx]) -- this gives 0.6
This post has been edited by t.heisler: May 28 2020, 02:55 PM
Go to the top of the page
 
FrankRuperto
post May 28 2020, 02:57 PM
Post#5



Posts: 1,112
Joined: 21-September 14
From: Tampa, Florida USA


Stay away from floating point datatypes, they generate skewed results.

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
GroverParkGeorge
post May 28 2020, 02:57 PM
Post#6


UA Admin
Posts: 37,510
Joined: 20-June 02
From: Newcastle, WA


Format() returns a string, not a number, and I wouldn't introduce it here. I think that actually may be complicating things.

Currency should be accurate to four decimal places by itself.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ADezii
post May 28 2020, 03:00 PM
Post#7



Posts: 3,089
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. If you are interested in accuracy up to 4 Decimal Places, then converting the Value to Currency should do the trie=ck:
    CODE
    ? CCur(50.60)-CCur(49.95)
    0.65

    CODE
    ? CCur(50.609)-CCur(49.957)
    0.652

    CODE
    ? CCur(50.4832)-CCur(47.7779)
    2.7053
  2. Unless I am missing something, if the Fields are defined as CURRENCY, then there should be no issue.
    Value1Value2
    87.8784.19
    56.1354.99
    49.9647.99
  3. SQL Definition:
    SQL
    SELECT tblDemo.Value1, tblDemo.Value2, [Value1]-[Value2] AS Diff
    FROM tblDemo;
  4. Query Output:
    Value1Value2Diff
    87.8784.193.68
    56.1354.991.14
    49.9647.991.97




This post has been edited by ADezii: May 28 2020, 03:13 PM
Go to the top of the page
 
t.heisler
post May 28 2020, 03:29 PM
Post#8



Posts: 20
Joined: 28-January 19



Thanks for the detailed reply, I think its just the format function.

Not sure why that was put in instead of the Round() function, I will probably just use that instead.

Maybe when the old backend was still access the format function behaved like expected and using the SQL currency fields is causing some of this to happen.
This post has been edited by t.heisler: May 28 2020, 03:29 PM
Go to the top of the page
 
projecttoday
post May 28 2020, 04:14 PM
Post#9


UtterAccess VIP
Posts: 12,403
Joined: 10-February 04
From: South Charleston, WV


You need to define your data as currency or decimal (not convert floating point to currency). Floating point shouldn't be used at all. If you did, in fact, define it as currency and you're getting this inaccuracy can you post it?

--------------------
Robert Crouser
Go to the top of the page
 
t.heisler
post May 28 2020, 05:49 PM
Post#10



Posts: 20
Joined: 28-January 19



The data is currency, letting the format function handle the subtraction without explicitly converting each value to a double is the problem it seems.
I'm just going to use the round function.

To see whats happening you can just enter this into any immediate window (these are my results)

?Format((50.60-49.95),"0.0")
0.6
?50.60-49.95
0.649999999999999
?Format((0.65),"0.0")
0.7

I can see whats happening now and know how to work around it, its just strange that Format((50.60-49.95),"0.0") doesn't give you the same result as Format((0.65),"0.0").

This link helped explain why this is happening:

Microsoft Article

This post has been edited by t.heisler: May 28 2020, 05:51 PM
Go to the top of the page
 
FrankRuperto
post May 28 2020, 06:21 PM
Post#11



Posts: 1,112
Joined: 21-September 14
From: Tampa, Florida USA


I suggest you round the floating point fields to 4 decimal places and then change the field types to currency. Leaving them as floating point types is going to cause more problems down the road.

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
t.heisler
post May 28 2020, 06:36 PM
Post#12



Posts: 20
Joined: 28-January 19



Sorry for the confusion, I don't have any floating point fields this rounded number is just to display on a report.
Go to the top of the page
 
kfield7
post May 28 2020, 06:59 PM
Post#13



Posts: 1,078
Joined: 12-November 03
From: Iowa Lot


Being of anal nature, I would like to clarify a couple things.

First, in this case, 0.649999999 is not inaccurate, nor is it imprecise, it's just not exact.

Accuracy gets you in the ballpark.
Precision tells you where in the ballpark you are.
Exactness says I'm on first base, not sliding into first, not leading off first, but only on first. In most cases, you don't need to know what seat the ball landed in to know you've hit a home run.
Then there's significant figures, for which there are specific rules that computers don't inherently understand.

You are taking measurements.
It appears your instrument may measure to +/- 0.005. 50 is accurate, but 49.95 is more precise.
I guarantee 49.95 is not exact, and thus neither is 0.65.
0.65 and 0.64999999 have the same accuracy, but 0.64999999 falsely shows a greater precision, because computers don't understand significant figures, and floating point math is done in binary rather than base 10.
If you either format or round your result to the same precision as your instrument, you will get the expected answer.
It takes a ton of calculations for 0.64999999 to not round to 0.65, so even without rounding, you can most likely use the result of 0.64999999 in other calculations, and applying the same precision get the answers you expect. This of course is not good enough for banking, because there may be billions of sums performed to get to a result, hence the need for a currency type, with the appropriate math ops.
Go to the top of the page
 
projecttoday
post May 28 2020, 09:11 PM
Post#14


UtterAccess VIP
Posts: 12,403
Joined: 10-February 04
From: South Charleston, WV


I think the point is don't define a field yourself in your tables or in you code as floating point (single or double).

--------------------
Robert Crouser
Go to the top of the page
 
Gustav
post May 29 2020, 05:12 AM
Post#15


UtterAccess VIP
Posts: 2,269
Joined: 21-February 07
From: Copenhagen


There is, of course, nothing wrong in storing values with many decimals as Double. What matters, however, is how you handle these.
Due to the nature of Double, you cannot do subtractions just like that - as you have noticed.

If you have to, and the values are not so extreme, that only Double can hold these, just apply CDec, for example:

CODE
? CDec(50.6012345678) - CDec(49.9512345678)
0.65


If you don't need more than four decimals, store the values as Currency. Then you can perform subtractions directly.

If you need to round the measurements, and the calculations are critical, then avoid the native VBA.Round which is quite buggy and study my project VBA.Round which covers the most demanding requirements for rounding.

--------------------
Microsoft Office 365 (Access) MVP 2017 ->
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    13th July 2020 - 05:51 AM