baffled by select case situation (1 Viewer)

[CraigDolphin]

Hi folks.

I'm having an issue with some nested select case statements that aren't returning the expected result

the code is in a public function called QualityAssurance and the top-level select case appears to be working correctly because the result is coming from the appropriate part of the code at that level.

The select case statement that is giving me the wrong result is

    Select Case mydifference
        Case Is <= 0.1
        QualityAssurance = "Good"
        Case Is <= 0.2
        QualityAssurance = "Fair"
        Case Is > 0.2
        QualityAssurance = "Poor"
    End Select

I supply the function with two values, and the variable mydifference is set using the equation

mydifference = Abs(value1 - value2)

both 'values' are of the double data type, as is the mydifference variable.

When the two values supplied to the function are 3.4 and 3.3 the mydifference variable = 0.1 but the function's output is 'Fair' rather than 'Good'.

I've checked to be sure the mydifference value is numeric at the point of the evaluation (it is). I've tried re-writing the case tests like:

    Select Case mydifference
        Case 0 To 0.1
        QualityAssurance = "Good"
        Case Is <= 0.2
        QualityAssurance = "Fair"
        Case Is > 0.2
        QualityAssurance = "Poor"
    End Select

But the result is the same. Shouldn't a mydifference = 0.1 evaluate to 'good'?What am I missing?

 

[KenHigg]

just a quickie - Have you stepped through the code to see what the actual values are?

Can you omit the 'is'

??
ken

 

[WayneRyan]

Craig,

I don't have enough info here, but I'd suspect that your difference of 0.1
is really a difference of 0.1000000001 or something like that.

I think you might have to explicitly use the Round function.

Wayne

 

[DJkarl]

Try declaring the mydifference variable as a Single instead of a double.

 

[KenHigg]

That makes a lot more sense than my thoughts -

Wayne to the rescue again


ken

 

[pbaldy]

I'm with Wayne; it's a floating point problem. I was going to suggest using a different data type, but the Round function would also solve the problem.

 

[petehilljnr]

If you were to bung the result of value1 - value2 into a field in a table that was double and expand out all of the decimal places, you'd see that 0.1 will be displayed as 0.1000000000000000378 (or something similar). Dunno why that happens, but I've been caught out many times in similar scenarios.

The function is actually performing correctly, except what is being displayed (or rather what you expect 3.4 - 3.3 = 0.1) is not actually what is being passed through the select statement.

I would use :

Select Case Round(mydifference,1)
Case 0 To 0.1
QualityAssurance = "Good"
Case Is <= 0.2
QualityAssurance = "Fair"
Case Is > 0.2
QualityAssurance = "Poor"
End Select

Or - you could multiply mydifference by 10 and test for whole values (1,2,3) instead.

Regards,
Pete

 

[petehilljnr]

I'm too slow ...

 

[boblarson]

I'm too slow ...

No Pete - not too slow - "deliberate"

 

[CraigDolphin]

Wow. Thanks for all the feedback.

The first suggestion I tried was the Round function which worked like a charm. Then, out of curiosity, I tried changing the datatype of the variable to Single (without using Round()) and that also worked like a charm.

I then looked at the values to 15 decimal places and they were 3.400000000000000 and 3.300000000000000 so I really don't understand why a floating point problem exists. But, thanks to you all I can work around it

Thank you all again for your helpful advice.

 

[John_W]

Custom rounding function here to help in this circumstance:

http://www.mvps.org/access/modules/mdl0054.htm