Abnormality (1 Viewer)

[doulostheou]

I came across something I don't understand. I think I could work around it, but I don't understand what is going on.

I create a function to round a given number to the nearest fifth:

Function RoundToFifth(dblNumber As Double) As Single
    Dim intNumber As Integer
    Dim dblRemainder As Double
    'To use this function with time: RoundToFifth((End-Start)*24)
    
    intNumber = Fix(dblNumber)
    dblRemainder = (dblNumber - Fix(dblNumber))
    
    dblRemainder = dblRemainder * 2
    dblRemainder = Round(dblRemainder, 1)
    dblRemainder = dblRemainder / 2
    
    RoundToFifth = intNumber + dblRemainder
End Function

I am using this function in a query to round the difference of two time values to the nearest fifth of an hour.

So, in the immediate window, I try the function out:
print roundtofifth((#3/10/05 3:05:56 PM#-#3/10/05 3:03:32 PM#)*24) produces .05. Works perfectly.

I put the same into my query, doing the calculation from the same two values stored in the table:
Test: RoundToFifth(([End]-[Start])*24) produces 0.0500000007450581 with the End and Start values being exactly the same.

I am going to add a round statement to the query to round to 2 decimals, as the value that is being produced in error is always off by a very minute amount. So that will solve my error, but I would really appreciate it if someone could explain what is happening.

 

[Pat Hartman]

The problem is that double precision numbers cannot always be converted exactly to decimal numbers. To understand this, read the article entitled "When Access Math doesn't add up" found at www.fmsinc.com The other articles are well worth reading also.

 

[doulostheou]

I really appreciate the link. The article was very interesting. I have already replaced every instance of Round() in my code. I'm still not sure why I would get one result in the intermediate window and a different result in the query, but I do think I can safely chalk it up to the rounding errors discussed in the article.