How to avoid rounding when comparing numbers?

[Maury Markowitz]

We're moving data from an Excel sheet into a table in our DB. We copied the
data over in the past, but recently found that it was all cut off at the 4th
decimal place. This did not occur in the DB itself as far as I can tell, the
fields are set to FLOAT, not MONEY.

Anyway I've written a macro to roll over the original data again, find if
there's a single matching row in the DB, and if so, update it. As a sanity
check I look at the values in the DB just to be sure we have to change
something. For instance...

if rstTrans!Value = theTotal then...

this test always passes, even when the numbers are actually different! For
instance, in the first case the number on the left is 1,933.46385, and the
one on the right is 1,933.4639

So how do I get VB to properly compare these things?

Maury

 

[Udo]

Try to simply divide the numbers. If the result is not exactly 1, then
there is a difference.

Udo

 

[Maury Markowitz]

Try to simply divide the numbers. If the result is not exactly 1, then
there is a difference.

That doesn't really help. When I read the cell value, even though it clearly
has a five-decimal-place number in it, the value returned in VB is four
decimals long. In other words if I try to fix the database, it gets "fixed"
with the wrong number.

Why is it that when I refer to the cell I get a rounded number?

Maury

 

[Udo]

Most probably the problem is the initialization of the variables. The
following piece of code gives the correct result and shows in the
local-window all the decimals which are appropriate. The numbers I used
were: First = 3,4587921, Second = 3,4587919.

Dim First As Double
Dim Second As Double
Dim Result As Double

First = Cells(8, 3)
Second = Cells(8, 4)
Result = First / Second
If Result <> 1 Then
MsgBox ("Not identical")
End If

Good luck
Udo