Losing Accuracy

[irishjoe]

Hi,

I have encountered a problem and was wondering if anyone has come across one similar.
I have a query running a function called GetYesNoScore (See below) with various parameters. It is a function to see if a field is a “1” and if so, count up the weighting.
So, if I run, GetYesNoScore(2,0.2,1,0.3,1,0.5), I should get a score of 0.8. This is because it finds a 1, then adds the weight to the score. Eg, 0.3 + 0.5 = 0.8.

This all works fine but when the number is shown in the query, it comes up as 0.800000011920929. Even when running through the code and doing a mouse-over, the score comes up as 0.8 but access seems to be losing accuracy when sending the information back to the query.

Does anyone have any advice on the matter?
Thanks for reading this.


Public Function GetYesNoScore(Q1 As Integer, W1 As Single, Optional Q2 As Integer, Optional W2 As Single, Optional Q3 As Integer, Optional W3 As Single, Optional Q4 As Integer, Optional W4 As Single) As Single

Dim score As Single
score = 0

If Q1 = 1 Then
score = score + W1
End If
If Q2 = 1 Then
score = score + W2
End If
If Q3 = 1 Then
score = score + W3
End If
If Q4 = 1 Then
score = score + W4
End If

GetYesNoScore = score

endif

 

[KenHigg]

Did you try rounding stuff off?

 

[irishjoe]

Yeah, I tried adding in "score = Round(score,1)" but it doesn't make any difference.
Everything is fine in the function but after that, when it appears in the query, it loses accuracy.

 

[KenHigg]

What about rounding the GetYesNoScore() in the query?

 

[irishjoe]

Yeah, I tried it like this...
round(GetYesNoScore1,0.2,..etc..),1)
But it doesnt seem to do anything different.

I also tried making it into a function so no avail. (Below)

Public Function roundMe(val As Single) As Single

roundMe = Round(val, 2)

End Function

 

[KenHigg]

Can you put the table, function and query in a mdb and post it?

 

[irishjoe]

There you go.
I have stripped away all useless information from the database to make it smaller.

Thanks for looking at it.

 

[The_Doc_Man]

The original problem is that your number isn't exact once you store it in a single or double because it contains an infinitely repeating representation.

The number 0.8 cannot be expressed exactly in binary. This number is really something like 8 x (10 ^ -1 ) - and it is the (10 ^ -1 ) that eats your socks for you. One tenth is an infinitely repeating BINARY fraction, something like

0b00.0001100110011001100110011001100....110011001100 {ad nauseam}

Or if you prefer hexadecimal, 0d00.1 is really 0x0.199999999999999999...

So 8 * 0.10 {decimal} is going to be (in binary)

0b01000.0 * 0b00.0001100110011.....

which comes out to

0b00.11001100110011001100110011001100.... {do I really need to go on...?}

Or in hex, 0x0.CCCCCCCCCCCCCCCCCCCCCCCC....

There ain't enough bits in the WORLD to exactly express 0.8, and you are seeing the consequences of it.

NOW.... how do you deal with it? Well, learn the difference between raw displays and formatted displays. What you see if you set a display template in table is a FORMATTED display - but when you manipulate the data in the table, it still has the "stragglers" in it. Because they really ARE there as an artifact of that method of storage. A format on a table doesn't alter the contents in the least. As a query would show, 'cause a query can have different formatting from its table, pretty much at designer's whim.

Rounding doesn't seem to work right because.... are you ready for this? Round 0.800000011920929 to 0.1, why don't you...? But as soon as you generate the number, you have the same number back again because fractions rounded to a tenth are still based on a tenth, which is STILL a repeating binary fraction, as inexact as it ever was!

If you anticipate that you will never have a weighting factor that results in fractional parts other than 0.00 through 0.99,
then always display the result FORMATTED (not rounded with a function - that's a waste of time most of the time...).

Use FORMAT$( x, "######.##" ) or something like that. Look up user-defined numeric formats for things like leading-zero control, comma-separation techniques, and the like. FORMAT will do the required STRING TRUNCATION (and I meant that exactly as I said it...). Given that you are dealing in decimal fractions in binary, you will NEVER EVER IN A BAZILLION YEARS get rid of all the straggler digits for any fraction that isn't a straight fraction of a power of 2 and some rare special case variants thereof.

0.5, you can express exactly. 0.75 also makes it. (Because 0.25 is exact, too...) You can figure out the others. But 0.1000 ? Fergeddaboudit!

 

[KenHigg]

Hum... I think it has something to do with the relationships of the tables...

 

[irishjoe]

So the lesson is:
Don't use single or double.
I had the idea of multiplying everything by 10 which would mean that I could use ints. Then in the query, divide it by 10. I wanted to try and get a better way but I think i'll have to do it.

Thanks for your help guys.

 

[Meltdown]

Attached the solution...you need to use the CDec function to create a Decimal data type. Decimal data types only accept Variant variables.

 

[irishjoe]

I was dabbling with the idea but was put off by the speed of the processor with variants. In your experience, how much slower is the operations when using variants rather than normal types?
The database is doing 1 section. I have about 10 sections and over 1500 records.

Do you think it would be quicker using a variant or using integers and dividing by 10?

 

[Meltdown]

Sorry, I really don't know, without testing.

One other thing, in my example I changed the W1, W2 etc variables to Variant, you can change them back to Single, it's only the Function that needs to be declared Variant

 

[KenHigg]

Hum... I think it has something to do with the relationships of the tables...

Hum... My bad.

Doc - Can you start back at the top and go over this again. A small bit at a time...

 

[The_Doc_Man]

Doc - Can you start back at the top and go over this again. A small bit at a time.

No.

OK, I'll make another suggestion, more direct this time.

The problem is that a real (single,double) representation of a decimal fraction is, with few exceptions, an indefinitely repeating number. Since the weighting factors can contain decimal fractions, you cannot expect to get exact results. EVER. It is an inherent limitation of the hardware representation of decimal fractions on a binary machine. Apples and orange, resulting in fruit salad. Only two machines I've ever worked on DID NOT have this limit and neither one is in use these days... IBM 1620 series and RCA 300 series. I know there were a few others, but today, we got x86 machines with their funky binary format that resembles the old IBM 7000 series hardware. (Yeah, I know... I just gave away my age. )

SO... there are two possible solutions, each taking a different direction.

1. Leave all numbers in single/double. Live with the fact that they are always and forever inexact due to internal representation limits. BUT when displaying the numbers, always use an explicit FORMAT statement. Who cares what they look like when you AREN'T looking at them, after all? Hmmm... that suggests a ZEN question: If no one is looking at it when you compute it, does it have any digits at all? (Did I just hear a tree fall?)

The FORMAT$ processor will force rounding of the number when it tries to generate the string representation. Remember, the first several digits after .8 were zeros. Six of them, if I counted correctly. So if you only keep about, say, 2-4 digits, the FORMAT processor will be forced to round or truncate, and the first digit to be removed is 0, so it will truncate.

2. Pick another representation that doesn't have this problem. And as it happens, there IS such a happy solution. Currency is an INTEGER (not a fraction) that happens to be SCALED to (up to) 4 places. Scaled integers do not have the same problem about indefinite fractional representations. (They have other problems - but not that one.) Then you just have to be sure that they don't show a currency symbol when you output the value.