Select Case Numeric issue (1 Viewer)

[Lister]

Hi Guys, having a wee bit of bother with a select case statement handling a numeric range of values. Basically I’ve quite a bit of data that needs to be summarised. Per record, where each record could have 20 to 2000 readings with the same record number. There is a range, First Range: < -0.1, Second Range: >= -0.1 AND <= 0, Third Range >0

A sample of data would look like…
-0.14
-0.13
-0.07
-0.02
-0.01
0
0.02
0.17
0.18
0.24

So simple enough, count the number of readings for each record, count the number in each Range and you have the percentage of each range for each record.
But I’m having issue with the code I’m running from a recordset

            dblHolder = rs!skid_esc_rv
            intCount = intCount + 1
            Select Case dblHolder
            Case dblHolder < -0.1
                intBelow_TL = intBelow_TL + 1
            Case dblHolder - 0.1 To 0
                intBetween_TL_IL = intBetween_TL_IL + 1
            Case dblHolder > 0
                intAbove_IL = intAbove_IL + 1
            Case Else
                MsgBox rs!skid_esc_rv
            End Select

I’m passing the reading value through to a double integer numeric variable but I just can’t seem to get the code to run correctly.
If someone could have a look and spot the no doubt obvious bug that would be great.

Thanks

 

[John Big Booty]

Is that all the code you are using or are you stepping through the Records Set in a part of the code that you've not posted

 

[Lister]

I did think it was all the code that was relevant. It’s the select case statement that is generating the error.
The recordset SQL statement creates a huge recordset with looks like…
Surf_id, skid_esc_rv
2001, -0.01
2001, -0.01
2001, -0.14
2001, 0
2001, -0.12
2002, 0
2002, -0.01

Nothing exceptional, by looping through the recordset the code is just going to count the number of each “Surf_id” and the number that fall within each range, once its don’t that I’ll convert to a percentage. Once the Surf_id changes the results are saved and the loop continues from 0 again.

But the select case statement is falling. For example a value of “skid_esc_rv = 0” will not make it past the “Case dblHolder < -0.1”

So I’m quite sure I’m not using the correct parameters within the select case statement.

Hoping this makes sense

 

[John Big Booty]

Try;

            dblHolder = rs!skid_esc_rv
            intCount = intCount + 1
            Select Case dblHolder
            Case IS < -0.1
                intBelow_TL = intBelow_TL + 1
            Case  - 0.1 To 0
                intBetween_TL_IL = intBetween_TL_IL + 1
            Case IS > 0
                intAbove_IL = intAbove_IL + 1
            Case Else
                MsgBox rs!skid_esc_rv
            End Select

from here

 

[Lister]

Cheers John, knew it was that bloody select case syntax that was causing the error. Just couldn't work it out. Thanks again mate, very helpful.

 

[John Big Booty]

Glad you got it sorted