CODEWITHSUNDEEP
vbams-access
Diego
Diego

Reputation: 379

Comparing doubles returns false

I have three numbers from my database and want to compare them in an if statement.

I have a simple conevert function that returns only doubles.

Public Function RetDbl(ByVal obj As Variant) As Double
    On Error Resume Next
    RetDbl = val(Replace(Nz(obj, 0), ",", "."))
End Function

The statement is 

If RetDbl(rs.value("NumA")) + RetDbl(rs.value("NumB")) <> (RetDbl(rs.value("NumC")) * 1000) Then
    '[... do some code ...]
End If

With RetDbl(rs.value("NumA")) = 0.33, RetDbl(rs.value("NumB") = 0.5 and RetDbl(rs.value("NumC")) = 0.00083

This always returns false

I also tried:

In the direct field (STRG + G): ?cdbl(0.33) + cdbl(0.50) = cdbl(0.83) returns false. When i leave out the last part it returns 0.83

How can i compare these numbers?

Upvotes: 2

Views: 1761

Answers (2)

Vityata
Vityata

Reputation: 43593

Comparing floating point numbers is really an issue, if you try to do it without understanding of the nature of the floating numbers.

Here is a nice article about it - https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html and How dangerous is it to compare floating point values?

In general, this problem is so big, that some languages like C# have developed a specific class called Decimal which makes comparing run as it would be expected by a non-programmer. Decimal info. In VBA, a similar class is Currency. Thus

CCur(0.33) + CCur(0.50) = CCur(0.83)

Returns True. VBA supports the function CDec, which converts a double to a decimal number, but it does not support the class Decimal. Thus:

CDec(0.33) + CDec(0.50) = CDec(0.83)

would also return True. And is with some better accuracy than Currency. CDec documentation.

enter image description here

Upvotes: 2

Erik A
Erik A

Reputation: 32682

Comparing floating numbers is hard. Only yesterday, I've posted this question

My solution:

Public Function DblSafeCompare(ByVal Value1 As Variant, ByVal Value2 As Variant) As Boolean
    'Compares two variants, dates and floats are compared at high accuracy
    Const AccuracyLevel As Double = 0.00000001
    'We accept an error of 0.000001% of the value
    Const AccuracyLevelSingle As Single = 0.0001
    'We accept an error of 0.0001 on singles
    If VarType(Value1) <> VarType(Value2) Then Exit Function
    Select Case VarType(Value1)
        Case vbSingle
            DblSafeCompare = Abs(Value1 - Value2) <= (AccuracyLevelSingle * Abs(Value1))
        Case vbDouble
            DblSafeCompare = Abs(Value1 - Value2) <= (AccuracyLevel * Abs(Value1))
        Case vbDate
            DblSafeCompare = Abs(CDbl(Value1) - CDbl(Value2)) <= (AccuracyLevel * Abs(CDbl(Value1)))
        Case vbNull
            DblSafeCompare = True
        Case Else
            DblSafeCompare = Value1 = Value2
    End Select
End Function

Note that the AccuracyLevel (epsilon) could be set to a smaller value, and I'm using the same value for singles and doubles, but it did well for my purposes.

I'm using a relative epsilon, but multiplying it with the first, and not the largest value, since if there's a significant difference the comparison will fail anyway.

Note that I'm using <= and not < since else DblSafeCompare(cdbl(0) ,cdbl(0)) would fail

Note that this function checks for type equality, so comparing integers to longs, doubles to singles, etc. all fails. Comparing Null to Null passes, however.

Implement it: 

?DblSafeCompare(cdbl(0.33) + cdbl(0.50) ,cdbl(0.83))
?DblSafeCompare(cdbl(0.331) + cdbl(0.50) ,cdbl(0.83))

Upvotes: 3

Related Questions