# Thread: Inexact floating point representations

1. ## Inexact floating point representations

What is the correct way to deal with this problem?

I have this line of code:

If thisMeasureTime <> measureTime Then

In my mind as it appears in my code I am expecting both variables to be 1
but it must be 0.999999999 vs 1

I changed the above test to

If Abs(thisMeasureTime - measureTime) >= 0.000001 Then

and it works as I expect.

Have I dealt with this correctly,
or have I just produced a kludge that works, and there is a proper way to deal with this?

2. ## Re: Inexact floating point representations

Floating numbers are stored as binary fractions. This means they cannot hold an exact representation of any quantity that is not a binary fraction (of the form k / (2 ^ n) where k and n are integers). For example, 0.5 (= 1/2) and 0.3125 (= 5/16) can be held as precise values, whereas 0.2 (= 1/5) and 0.3 (= 3/10) can be only approximations.

So your 'kludge' is both appropriate and necessary to get a meaningful comparison of floats.

While we're at it, remember any operations on floats (+, -, /, *, etc) - not just comparisons - can also lead to annoying rounding errors and must be coded for appropriately.

3. ## Re: Inexact floating point representations

Originally Posted by mms_
Have I dealt with this correctly,
or have I just produced a kludge that works, and there is a proper way to deal with this?
This is the proper way to deal with floating-point comparisons -- by using a Private Const EPSILON As Double = 0.000001 for the margin of rounding errors.

a = b becomes Abs(a - b) < EPSILON
a <> b becomes Abs(a - b) > EPSILON
a < 0 becomes a < -EPSILON
a <= 0 becomes a < EPSILON
a < b becomes a < b - EPSILON
a <= b becomes a < b + EPSILON

Cannot come up with more examples now but you can use common sense when faced with different use-cases.

cheers,
</wqw>

4. ## Re: Inexact floating point representations

OK thanks guys

5. ## Re: Inexact floating point representations

mms_, basically, you did it correctly. And personally, I wouldn't call that particular case a kludge. It just appreciates the sometimes peculiar nature of IEEE numbers.

6. ## Re: Inexact floating point representations

Sadly, EPSILON does Not afford a simple solution for Database dates (a double) when "Seeking" (a = compare). One either has a Match or a NoMatch
with no way to compare until after the Seek.

7. ## Re: Inexact floating point representations

Originally Posted by vb6forever
Sadly, EPSILON does Not afford a simple solution for Database dates (a double) when "Seeking" (a = compare). One either has a Match or a NoMatch
with no way to compare until after the Seek.
SQL (as a language) offers all the comparison-options wqweto listed further above.

Your "Seeking" is only "reduced client-functionality", usually offered by clientside Result-Set-Wrappers (e.g. DAO-Recordset-Seeks, or ADO-Rs-Filter-Methods) -
and therefore often limited with regards to the complexity of "allowed boolean expressions" ...
(which are easily handled without limitations in the Where-Clauses of "full SQL-parsers/interpreters" of the DBEngine-Cores).

Olaf

8. ## Re: Inexact floating point representations

Olaf:
Thanks for pointing out that "Seeking" is "reduced functionality".

I do a lot of DAO table Seeks on DateTime fields and never understood why
"Seek" didn't offer this capability. Seems like going around the block to
query the table, make the comparison, and then if equal -- but off enough because of the float precision the record is considered a new record to the DB --, having to adjust the datetime value being compared, in order to edit the table record.

Here's a little example using EPSILON (at .0000001) when things appear equal (d1 and d2 are displayed the same) but internally they are NOT, and the databases normally interpret them as NOT equal.

Code:
```Private Sub Form_Load()

Dim d1 As Date
Dim d2 As Date
Dim n1 As Double
Dim n2 As Double

d1 = #1/10/2020 3:30:33 AM#
n1 = CDbl(d1)

n2 = n1 + 0.0000001
d2 = CDate(n2)

MsgBox "d1: " & d1 & vbCRLF & "d2: " & d2

If n1 = n2 Then
MsgBox "Dates Match"
Else
MsgBox "NoMatch  " & vbCRLF & "n1: " & n1 & vbCRLF & "n2: " & n2
End If

End Sub```
https://floating-point-gui.de/errors/comparison/
https://bitbashing.io/comparing-floats.html

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

Featured