Finding the highest value

[w11184]

I have a very simple question. Basically I have written a piece of code that loops through a field and identifies the numbers and depending on what the number is it sets it to one of the option1, option2 etc variables.

But when I run the code, I can't get the option variables to reset when it moves onto the next field. So if the first field contains the numbers 2,3 and 4 but the second only contains 2 and 3 it'll still display 2,3 and 4.

I have included my code below but please let me know if this doesn't make sense.

Public Sub HigherValue()

    Dim option1, option2, option3, option4, option5  As Integer
    option1 = 0
    option2 = 0
    option3 = 0
    option4 = 0
    option5 = 0
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fld As Field
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT ComputerUse FROM tblTestData WHERE ComputerUse Like '(-5*'", dbOpenDynaset)

    Do While Not rs.EOF
    
        For Each fld In rs.Fields
            
            For i = 1 To Len(fld)
                If IsNumeric(Mid$(fld, i, 1)) Then tempValue = CInt(Mid$(fld, i, 1))
       
                Select Case tempValue
                Case 1: option1 = 1
                Case 2: option2 = 2
                Case 3: option3 = 3
                Case 4: option4 = 4
                Case 5: option5 = 5
                End Select
            Next
            
            Debug.Print option1
            Debug.Print option2
            Debug.Print option3
            Debug.Print option4
            Debug.Print option5
            Debug.Print " "
            
            
        Next 'END FOR EACH
        rs.MoveNext
        
    Loop

End Sub

 

[jzwp22]

You will have to set the variables back to zero after you are done with them but before you move to the next field

Do While Not rs.EOF
    
        For Each fld In rs.Fields
            
            For i = 1 To Len(fld)
                If IsNumeric(Mid$(fld, i, 1)) Then tempValue = CInt(Mid$(fld, i, 1))
       
                Select Case tempValue
                Case 1: option1 = 1
                Case 2: option2 = 2
                Case 3: option3 = 3
                Case 4: option4 = 4
                Case 5: option5 = 5
                End Select
            Next
            
            Debug.Print option1
            Debug.Print option2
            Debug.Print option3
            Debug.Print option4
            Debug.Print option5
            Debug.Print " "
            
            option1=0
            option2=0
            option3=0
            etc.

        Next 'END FOR EACH
        rs.MoveNext
        
    Loop

 

[tehNellie]

The obvious point to make is that you never reset the variables back to 0. The other is that only Option5 is an integer, the rest are variants.

Dim opt1, opt2 as Integer

Only sets the last variable as an integer datatype, because nothing is specified for Opt1 it's a variant.

TempValue is also a variant because it's not declared anywhere, Option Explicit set at the top of your module directly below OPTION COMPARE DATABASE will help with those.

You're setting the option variable for each character in each field but only outputting them when you've stepped through the entire field and never resetting them back again.

After you've done your debug.print you need to set each option variable back to 0 again.

[edit] Didn't notice that there's only one column in the output which does kind of make the loop "For each fld" redundant as there's only one field.

 

[w11184]

Hi thanks for the reply.

So I have explicitly declared the option variables as integer and I have set them back to 0 after print (I have actually tried this before) but it still doesn't work...

my code now looks like this

Public Sub HigherValue()

    Dim option1 As Integer
    Dim option2 As Integer
    Dim option3 As Integer
    Dim option4 As Integer
    Dim option5 As Integer
    option1 = 0
    option2 = 0
    option3 = 0
    option4 = 0
    option5 = 0
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fld As Field
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT ComputerUse FROM tblTestData WHERE ComputerUse Like '(-5*'", dbOpenDynaset)

    Do While Not rs.EOF
    
        For Each fld In rs.Fields
            
            For i = 1 To Len(fld)
                If IsNumeric(Mid$(fld, i, 1)) Then tempValue = CInt(Mid$(fld, i, 1))
       
                Select Case tempValue
                Case 1: option1 = 1
                Case 2: option2 = 2
                Case 3: option3 = 3
                Case 4: option4 = 4
                Case 5: option5 = 5
                End Select
            Next
            
            Debug.Print option1
            Debug.Print option2
            Debug.Print option3
            Debug.Print option4
            Debug.Print option5
            Debug.Print " "
             
             option1 = 0
             option2 = 0
             option3 = 0
             option4 = 0
             option5 = 0
            
        Next 'END FOR EACH
        rs.MoveNext
        
    Loop

End Sub

And if it helps this is what my data look like.

ComputerUse
(-5) 3 and 4
(-5) 2 And 3 And 4
(-5) 2 and 3


And this is the result when I run the code.

HigherValue
0
0
3
4
5

0
2
3
4
5

0
2
3
4
5

 

[tehNellie]

And if it helps this is what my data look like.

ComputerUse
(-5) 3 and 4
(-5) 2 And 3 And 4
(-5) 2 and 3


And this is the result when I run the code.

HigherValue
0
0
3
4
5

0
2
3
4
5

0
2
3
4
5

The only one there that looks wrong is the last one, how is 4 getting set?

Look at the strings that you're comparing there.


(-5) 3 and 4
contains a 5 (set) a 3 (set) and a 4 (set)

(-5) 2 And 3 And 4
contains a 5,2,3,4 all of which are getting set.

(-5) 2 and 3
Not sure here Where the 4 is getting set in your code.

[edit]
yes I do, you're not resetting tempvalue:

            For i = 1 To Len(fld)
                If IsNumeric(Mid$(fld, i, 1)) Then tempValue = CInt(Mid$(fld, i, 1))
       
                Select Case tempValue
                Case 1: option1 = 1
                Case 2: option2 = 2
                Case 3: option3 = 3
                Case 4: option4 = 4
                Case 5: option5 = 5
                End Select
    
                [b]tempvalue = 0[/b]

            Next

 

[w11184]

The only one there that looks wrong is the last one, how is 4 getting set?

Look at the strings that you're comparing there.


(-5) 3 and 4
contains a 5 (set) a 3 (set) and a 4 (set)

(-5) 2 And 3 And 4
contains a 5,2,3,4 all of which are getting set.

(-5) 2 and 3
Not sure here Where the 4 is getting set in your code.

Yeah baffling me too. That's why I thought the variables are not resetting because if change my data to this:

(-5) 3
(-5) 2 And 3
(-5) 2 and 3 And 4

then this is what I get:

0
0
3
0
5

0
2
3
0
5

0
2
3
4
5

Which would be correct. However, if I move the 4 to the second record then the last record would show up as having a 4 as well!?

 

[jzwp22]

How about resetting tempvalue=0 as well.

 

[tehNellie]

How about resetting tempvalue=0 as well.

Just spotted and edited my previous post

 

[w11184]

Thank you so much guys, you're a life saver!

 

[tehNellie]

It does highlight an issue using variables in this manner, you only assign something to it if one half of the test is true (the character is a number) so you have to remember to reset it if the condition is false.

Both TempValue and for that matter the IF statement are unnessary, you could just use the Case Statement:

Select Case Mid$(fld, i, 1)
  Case 1 
    option1 = 1 
  Case 2 
    option2 = 2
  Case 3 
    option3 = 3
  Case 4 
    option4 = 4
  Case 5 
    option5 = 5
End Select

No variable, no IF and simpler, more explicit code.