Case Function

[houseofturner]

I am having an issue with some code - an excerpt of which is shown below.

Public Function FundedUnitsActualScore(intUnits As Single) As String

Select Case intUnits

Case Is < -50, Is < -59.9
FundedUnitsActualScore = 4

Case Is < -40, Is < -49.9
FundedUnitsActualScore = 5

Case 50 To 59.9
FundedUnitsActualScore = 14

This has been used to awards scores based on a particular value.

So for example, if the value is 50 then the points awarded is 14.

The above is just an example but it goes up and down in steps.

The trouble I am having is with the negative numbers.

If the value is 50 it correctly returns a value of 14: i.e. FundedUnitsActualScore(50) would return 14.

However if the value is -50 then it returns 5 rather than 4.

-50.1 returns 4 as I would expect so I assume it is something to do with number types?

The value is actual a much longer calculation but the principle is as described above.

It only seems to affect minus numbers which are whole - i.e. -50, -60 etc.

This function is used across a wide range of queries and databases so I really want to keep the same core function rather than find another way to do it but there must be something I am missing.

Any help would be much appreciated.

 

[pr2-eugin]

houseofturner, just that you know in Numerics.. -40 is Bigger than -50.. Try

Case -59.9 [B]To[/B] -50

 

[houseofturner]

houseofturner, just that you know in Numerics.. -40 is Bigger than -50.. Try

Case -59.9 [B]To[/B] -50

Hi thanks but I did try that and still got the same result. Any other thoughts?

 

[pr2-eugin]

I checked it, I seem to get the right result..

Am I missing something?

 

[houseofturner]

I checked it, I seem to get the right result..

Am I missing something?

Sorry, you are right I was being dim and put the values the wrong way round - thanks for your help.

 

[DavidAtWork]

Just to add, a Case statement applies the 'case' in the order you list each case, it is imperative you list them in the right order to get the right result. As soon as a condition is met, the case statement will exit and return said value.
Incidentally you don't have Case Else to catch all other values which are:
between -39 and +50
OR > +59.9
OR < -59.9
is that your intention

David