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

 

[alvechurchdata]

0.800000011920929

Access can't represent a fraction exactly and neither the 0.3 nor the 0.5 are being stored as those precise values. Use the Round() function if these extra digits are an embarrassment.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[irishjoe]

I have tried that. I have put a "round(score,1)" in the function being run and also tried putting it in the query.

Is this a what you would call a dirty bit? I have heard the term used before.

Both to no avail.

 

[JerryKlmns]

Or a trick
(learned here but can 't remember the member.name but he is a great one)

score = score + Int(W1*10)/10
if accuracy is 1 digit or

score = score + Int(W1*100)/100
if accuracy is 2 digit and so on

??????? ??? ????????????

 

[irishjoe]

Its still coming out with the long number.
I think the function is fine, its just access is doing something between finishing the function and inserting the value into the query.
I belive this to be the case because when I run through the code line by line, it is telling me that it is sending 0.8 back to the query, not 0.800000011920929.

 

[alvechurchdata]

it is telling me that it is sending 0.8 back to the query

It's lying - or at least it's not telling you the complete truth.

What happens if you say:

  GetYesNoScore = Round(score, 2)
[\code]

Geoff Franklin
[URL unfurl="true"]www.alvechurchdata.co.uk[/URL]

 

[Golom]

Several things to try.

The Currency data type (as opposed to single) does a better job of handling rounding. It is in fact a scaled integer rather than a floating point data type.

The "Round" function can produce some unexpected results. "Format" performs rounding the way we would usually expect it should be done.

Public Function GetYesNoScore(Q1 As Integer, W1 As Single, _
   Optional Q2 As Integer = 0, Optional W2 As Single = 0, _
   Optional Q3 As Integer = 0, Optional W3 As Single = 0, _
   Optional Q4 As Integer = 0, Optional W4 As Single = 0) _
   As [COLOR=red]Currency[/color]
    
    Dim score As [COLOR=red]Currency[/color]
    score = 0
    
    If Q1 = 1 Then score = score + W1
    If Q2 = 1 Then score = score + W2
    If Q3 = 1 Then score = score + W3
    If Q4 = 1 Then score = score + W4

    GetYesNoScore = [COLOR=red]Val(Format(score,"0.00"))[/color]

End Function

 

[irishjoe]

Still coming out with 0.8 before it goes to the query and the 0.800000011920929 in the query.

 

[PHV]

The problem is here:
Public Function GetYesNoScore(Q1 As Integer, ..., Optional W4 As Single)[highlight] As Single[/highlight]
Real values (Single and Double) aren't EXACT values but approximations.
Try to not type your function and to return the Rounded value as suggested by alvechurchdata.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Golom]

What is this "query" you keep mentioning? You've posted a Function on which comment has been made but I don't see any SQL. In what context are you using the function (i.e. calling it from SQL? From Code?)

 

[irishjoe]

Golom: Its on my third line of the original question.
"I have a query running a function called GetYesNoScore"

Your currency idea works but it has the currency symbol in it. Is there any way to get rid of it?