Select Case Statement not Working Right

[msaccess4me]

this code is not work right with [FirstOffldOperNo] value over 100
could anyone find out what is the problem?
thanks

see code below

Dim dbs    As DAO.Database
Dim rst    As DAO.Recordset
Dim strSQL As String
  Dim lngrecordcount As Integer
Dim strCurrentOperaNo As String
 
Set dbs = Workspaces(0).OpenDatabase("C:\AccessDBs\Post_Move_Ticket.accdb")
 
strSQL = "SELECT * " & _
             "FROM WODetail"
 
 
            Set rst = dbs.OpenRecordset(strSQL)    'Input Data
 
              'Get a count of the operations
    Set rst = Me!sfrmOpers_InNewTable.Form.RecordsetClone
    rst.MoveLast
    lngrecordcount = rst.RecordCount
 
 
            strCurrentOperaNo = InputBox("Enter Current Operation Number")
 
 
            If IsNull(strCurrentOperaNo) Or strCurrentOperaNo = "" Then 'Nothing entered
            Exit Sub
            End If
 
            Set rst = Me!sfrmOpers_InNewTable.Form.Recordset
            rst.MoveFirst
            Do While Not rst.EOF
            Select Case rst.Fields("FirstOffldOperNo")
 
            Case Is < strCurrentOperaNo
 
            rst.Edit
            rst.Fields("FirstOffldActive") = "0"
            rst.Update
 
            Case Is > strCurrentOperaNo
 
            rst.Edit
            rst.Fields("FirstOffldActive") = "1"
            rst.Update
 
            Case Is = strCurrentOperaNo
 
            rst.Edit
            rst.Fields("FirstOffldActive") = "-1"
            rst.Update
 
            End Select
 
            strCurrentOperaNo = strCurrentOperaNo
 
            rst.MoveNext
 
            Loop

[Rabbit]

It looks like you're working with strings. Convert the values to numbers before comparing them. Otherwise the comparison is on a character by character basis.

[msaccess4me]

the [FirstOffldOperNo]'s data type is "Long Integer", "Dim strCurrentOperaNo As Long Integer" is not taking the data type. Is there a way around it?

[Rabbit]

What do you mean by "not taking the data type", the phrase is vague.

[BikeToWork]

strCurrentOperaNo is a string in your code, but if you want to declare it as a long integer, first change the prefix to lng then try "Dim lngCurrentOperaNo as Long" Why not have a drop down of CurrentOperaNo values that the user can choose from? That would at least give you a little data validation.

[msaccess4me]

I tried "As Long" or "As Integer" they both get "type mismatch" error. I try to type in "As Long Integer" I get "Compile error: Expected: end of statement"

[BikeToWork]

The InputBox returns a string. Try CLNG() on the string output.

[NeoPa]

Try these changes :

Dim lngCurrentOperaNo As Long
...
lngCurrentOperaNo = CLng(InputBox("Enter Current Operation Number"))

They represent what has already been suggested but maybe you weren't understanding clearly enough.

Obviously, change all other references to the variable in your code in a consistent manner.

[msaccess4me]

thanks to all of you, I did try use CLNG(), it did not work for "Long Integer" data type, what I changed [FirstOffldOperNo]'s data type from "Long Integer" to "Single" and "Dim SigCurrentOperaNo As Single", make both data type the same, and it works good.
thanks again ^_^

[NeoPa]

Normally, from the info available, I'd expect Long to work better than Single. That is not to say that Single wouldn't work, but simply that it wouldn't be the best approach. To understand why Long doesn't work in your case we'd need access to your project or a better understanding of what you're actually working with.

Frankly, if you're happy with what you have, that's probably not worth the extra effort unless you think so.

[msaccess4me]

Is because [FirstOffldOperaNo] don't need decimals, Long will works faster?

[msaccess4me]

why on the table side, I only have "Long Integer" for data type to pick from the list for "Long", and on the coding side, when I try to "dim" a data type "Long Integer" is not available, only "Long" works, and "Long Integer" is not same data type as "Long" they are "type mismatch", I can not get it work.

[NeoPa]

In VBA the type name for Long Integer is Long (IE. Long means Long Integer in VBA). This is equivalent to a type of Single which means Single-Precision Floating Point.

As I said in my earlier post, to understand what is wrong in your particular setup (that stops Long from working for you) I would need either :

1. To be able to look at the project directly.
2. To have a fuller and clearer explanation from you which included the necessary details.

I would guess the latter is out of the question at this stage as we haven't had that by now. The former can also be quite complicated too, so only consider that if you're particularly interested in getting to the bottom of this.

[msaccess4me]

thanks a lot, I went back to change the data type to Long, and it works this time. It may been something I didn't do right last time when I tried. that's what happen when I try to change too many things at once. from this project, I get to understand more about data type. I am very appreciate all of your help.

[NeoPa]

MSAccess4Me:
That's what happen when I try to change too many things at once. From this project I get to understand more about data type.

Very wise comment. You're learning - which is always good.

Another very important thing to learn from this exercise is that it never makes sense to panic-change large amounts at one time. Take a step back; Go forward slowly and carefully. Always get one thing done completely and so you're happy with it, before you start the next. Like the tortoise, you'll get there quicker in the long run.