compare two doubles

[benjamin.weizmann]

hi

I have two values which are took from one table
usl define as double
lsl define as double

in for, I have a regular text box
the user type there his result

I want to check if the user value in the text box is between lsl and usl
I used with regular "<" ">"
but I understand it doesn't work

help please?

thanks
Ben

 

[jleach]

Hi - Doubles are floating point precision and shouldn't be directly compared without converting to fixed precision first. Offhand, I'm not sure the best way to do that (there's numerous means of doing so), but I'd bet a fair sum the issue is due to the floating point nature.

(try rounding them to a reasonable decimal precision and stuffing them in a Decimal type first)

 

[The_Doc_Man]

A problem with comparison statements in VBA is that behind the scenes, variables (and in this case I count the .Value of a control AS THOUGH it is a variable) get "promoted" or "demoted" by an implicit typecast call to a particular common data type. The typecast is done in a way that you don't know it has happened. So it is possible to get strange results as the result of the cast.

The BEST way to do what you describe is to take the value of the control in question and explicitly convert it using CDbl([control]) or perhaps CDbl(Val([control])) so that when you do your "lsl" and "usl" comparisons via < or > that you are comparing apples to apples without doubt as to that fact.

JLeach's suggestion goes the other way on conversions, but I think he must have missed the point that you are dealing with INEQUALITY. I doubt that rounding of the "usl" and "lsl" comparators is an issue unless they were the losers in the "hidden typecast" performed by VBA or unless they are so close together that their difference is lost to the significance limit of 15 digits for DOUBLE variables.

Having said that, I ALSO must point out that saying "it doesn't work" is not the best of all possible explanations. What makes you say that it doesn't work? Does it throw an error? Does it fail to show the correct / expected result? When it fails, does it take the wrong branch for the comparison? For what combination of numbers does it fail?

Give us an example of "usl" and "lsl" and the text-box value for which a failure occurs. Tell us more specifically what it does wrong.

 

[jleach]

Floating point errors are most common with equality operators, but can still present an issue with any comparison operator (if the tail end of the number is off a hair in either direction, it could produce erroneous results: two "equal" numbers should return false for < or >, but could be thrown off by floating point errors).

For control references, I've always preferred using the .Value property explicitly: = CDbl(Nz(Me.MyControl.Value, 0))

cheers,

 

[Pat Hartman]

If you can change the data type to currency, that is actually the best solution to get rid of the floating point errors. The limiation with currency is you only get four decimal places. If you need more precision than that, you are stuck with single or double.

Don't confuse the Currency data type with the Currency format. The Currency data type is actually a scaled integer (and that's why it doesn't suffer from floating point errors) and you can format it as standard, percent, or whatever other numeric format you want.

 

[The_Doc_Man]

I respectfully point out to my colleagues here that until Benjamin tells us WHY he thinks there is something wrong and until he gives us sample data, we have no certainty that the problem is floating-point related.

 

[Galaxiom]

The limiation with currency is you only get four decimal places. If you need more precision than that, you are stuck with single or double.

There is also Decimal which, like Currency, is stored as an integer with a scaling factor. It can precisely hold up to 28 significant digits (controlled by the Precision property). The Scale property controls the number of these digits after the decimal point.

It is defined under the FieldSize property of Number data type. Adjust Scale and Precision to suit your data.

I don't know if it is still the case, but sorting on a Decimal field used to be a problem. The workaround is to Index the field.

VBA also supports Decimal as a special type of Variant. Declare the variable as Variant then use CDec() to convert values to Decimal during the storage.

Private Sub demo()

Dim d As Variant
Dim s As String
Dim dd As Double

s = "1.11111111111111111111111111111111111111111111"

d = CDec(s)
Debug.Print d

dd = CDbl(s)
Debug.Print dd

End Sub

 

[Pat Hartman]

I always forget about the Decimal data type. In earlier versions of Access, there were problems with it. I don't remember what they were it was very long ago and they are most likely fixed by now.