Double type equality comparison failing?

[sqlvs]

Good citizens of Gotham,

I'm encountering an odd problem when comparing two values converted to
type double, as follows (please pardon the per-line script blocks):

<%="|" & CDbl(rsBatch("BatchTotal")) & "|" %><br>
<%="|" & CDbl(rsBatch("BatchPayments")) & "|" %><br>
<%= CDbl(rsBatch("BatchTotal")) = CDbl(rsBatch("BatchPayments"))
%><br>
<%= CDbl(rsBatch("BatchTotal")) - CDbl(rsBatch("BatchPayments"))
%><br>
<%= CDbl(rsBatch("BatchTotal")) - CDbl(rsBatch("BatchPayments")) = 0
%><br>

First two values (piping chars to ensure that unseen leading or
trailing spaces aren't present):

|126266.26|
|126266.26|
False
-1.45519152283669E-11
False

Second two values:
|43|
|80|
False
-37
False

For the second pair, "-37" is the difference between the two values
which, of course, are not equal. But why aren't the values in the
first pair equal? I have a feeling the clue is in the result
"-1.45519152283669E-11," and that I'm missing some fundamental piece
of obscurata here.

I ended up just using a string comparison (if CStr([rsvalue]) =
CStr([rsvalue])), but it seems like a comparison of the type
appropriate for dollar amounts (double) is the "right" way to do it.

Any ideas?

 

[Chris Hohmann]

Good citizens of Gotham,

I'm encountering an odd problem when comparing two values converted to
type double, as follows (please pardon the per-line script blocks):

<%="|" & CDbl(rsBatch("BatchTotal")) & "|" %><br>
<%="|" & CDbl(rsBatch("BatchPayments")) & "|" %><br>
<%= CDbl(rsBatch("BatchTotal")) = CDbl(rsBatch("BatchPayments"))
%><br>
<%= CDbl(rsBatch("BatchTotal")) - CDbl(rsBatch("BatchPayments"))
%><br>
<%= CDbl(rsBatch("BatchTotal")) - CDbl(rsBatch("BatchPayments")) = 0
%><br>

First two values (piping chars to ensure that unseen leading or
trailing spaces aren't present):

|126266.26|
|126266.26|
False
-1.45519152283669E-11
False

Second two values:
|43|
|80|
False
-37
False

For the second pair, "-37" is the difference between the two values
which, of course, are not equal. But why aren't the values in the
first pair equal? I have a feeling the clue is in the result
"-1.45519152283669E-11," and that I'm missing some fundamental piece
of obscurata here.

I ended up just using a string comparison (if CStr([rsvalue]) =
CStr([rsvalue])), but it seems like a comparison of the type
appropriate for dollar amounts (double) is the "right" way to do it.

Any ideas?

As Chris Barber pointed out, doubles are actually approximations of numbers.
In brief, because computers represent numbers (and everything else) in
binary, any number which cannot be represented as the sum of the powers of 2
must be approximated, similar to 1/3 in the decimal system. In the case of
dollar amounts you should consider using the currency data type (CCur). The
currency data type is a decimal with four (4) points of precision. If you
require greater precision, you will need to implement your own custom
decimal function/class/object, since VBScript does not support the decimal
data type.

HTH
-Chris

 

[Chris Barber]

Hey - I never knew about the currency 4 decimal place thing.

Damn. All these years I've been writing equality compare routines and
there's something that does it anyway.

Thanks Chris.

"Chris Hohmann" <hohmannATyahooDOTcom> wrote in message

Good citizens of Gotham,

I'm encountering an odd problem when comparing two values converted to
type double, as follows (please pardon the per-line script blocks):

<%="|" & CDbl(rsBatch("BatchTotal")) & "|" %><br>
<%="|" & CDbl(rsBatch("BatchPayments")) & "|" %><br>
<%= CDbl(rsBatch("BatchTotal")) = CDbl(rsBatch("BatchPayments"))
%><br>
<%= CDbl(rsBatch("BatchTotal")) - CDbl(rsBatch("BatchPayments"))
%><br>
<%= CDbl(rsBatch("BatchTotal")) - CDbl(rsBatch("BatchPayments")) = 0
%><br>

First two values (piping chars to ensure that unseen leading or
trailing spaces aren't present):

|126266.26|
|126266.26|
False
-1.45519152283669E-11
False

Second two values:
|43|
|80|
False
-37
False

For the second pair, "-37" is the difference between the two values
which, of course, are not equal. But why aren't the values in the
first pair equal? I have a feeling the clue is in the result
"-1.45519152283669E-11," and that I'm missing some fundamental piece
of obscurata here.

I ended up just using a string comparison (if CStr([rsvalue]) =
CStr([rsvalue])), but it seems like a comparison of the type
appropriate for dollar amounts (double) is the "right" way to do it.

Any ideas?

As Chris Barber pointed out, doubles are actually approximations of numbers.
In brief, because computers represent numbers (and everything else) in
binary, any number which cannot be represented as the sum of the powers of 2
must be approximated, similar to 1/3 in the decimal system. In the case of
dollar amounts you should consider using the currency data type (CCur). The
currency data type is a decimal with four (4) points of precision. If you
require greater precision, you will need to implement your own custom
decimal function/class/object, since VBScript does not support the decimal
data type.

HTH
-Chris

 

[sqlvs]

Chris * 2,

Thank you both for your responses.

I had considered using CCur but assumed I'd run into the same problem.
That seems like the "right" way to do it, though. A string conversion
would always work too, wouldn't it?

I haven't run into this problem before, which is strange--I'd think I
would have by now!

The city is safe once again, old chum.