first highest / lowest before decrease/increase in high / low nos

[cliffs]

I have the following the data in access 2003 database.

 sr No.  high    low        pline     dline   basetype   zonetype    
   1    1146     1142       1124      1126        1         1
   2    1136     1134       1128      1126        1         2
   3    1137     1130       1129      1125        1         2
   4    1147     1139       1131      1134        1         2
   5    1131     1128       1128      1130        1         1
   6    1135     1131       1129      1132        1         1
   7    1138     1130       1132      1134        1         1
   8    1149     1127       1134      1131        1         2
   9    1147     1129       1136      1132        1         1

Now I wnat to find out first maximum high for each sr.no with zonetype 2 and first maximum low for each sr.no. with zonetype 1. for eg :
sr.No. 1 with zonetype 1 first low after the current record would be 1130 because next record low is 1139 which is greater than 1130 and of course in next record low is 1128 which will be ignored for sr.NO.1 since the first low is 1130 before increase in the low no. Similarly for sr.No.2 with zonetype 2, first high after the current record would be 1147 because in next record high is 1131 which is lower than 1147 and subsequent high of 1149 in sr.no.8 will be ignored and so on. Hence, my desired output will be

sr.No. zonetype  result  difference in sr.no.
  1        1       1130   1-3=2 
  2        2       1147   2-4=2 
  3        2       1147   3-4=1 
  4        2       1149   4-8=4  
  5        1       1127   5-8=3
  6        1       1127   6-8=2
  7        1       1127   7-8=1
  8        2       1147   8-9=1

I have the following vba, which is not giving the desired output. Can u help me correcting the vba. thanks in advance

Sub calMHIGHnN6()
    Dim mydb As DAO.Database
    Dim myRS As DAO.Recordset
    Dim myCounter As Double
    Dim mycurrenthigh As Double
    Dim myprevioushigh As Double
    Dim mycurrentlow As Double
    Dim mypreviouslow As Double
    Dim mydz As Integer
    Dim mybase As Integer
    Set mydb = CurrentDb
    Set myRS = mydb.OpenRecordset("query14")
    
'   Loop through all records in table
    myRS.MoveLast
    myRS.MoveFirst
    Do While Not myRS.EOF
            
        mydz = myRS("zonetype")
        mybase = myRS("basetype")
        mycurrentcd1 = myRS("pline")
         mycurrentcd2 = myRS("dline")
         mycurrenthigh = myRS("high")
         mycurrentlow = myRS("low")
         period_count = 0
'   Check to see mfirst maximum high/low
            
            If mydz = 2 Then
              If mycurrenthigh > myprevioushigh Then
              myCounter = mycurrenthigh
                      Else
             If mycurrenthigh < myprevioushigh Then
                 myRS.MoveNext
                  myCounter = mycurrentcd1
              End If
            End If
            
            If mydz = 1 Then
                   
              If mycurrentlow < mypreviouslow Then
              myCounter = mycurrentlow
            
            Else
             If mycurrentlow > mypreviouslow Then
                myRS.MoveNext
                        
                       End If
           End If
           End If
            End If
                       
 '   Assgin counter value to the result
           myRS.Edit
         myRS.Fields("result") = myCounter
      
        myRS.Update
'   Assign current number to previous number and go to next record
        myRS.MoveNext
         
    Loop
    
'   Close recordset
    myRS.Close
    mydb.Close
    Set myRS = Nothing
    Set mydb = Nothing
    
    MsgBox "Done!"
End Sub

[zmbd]

cliffs
I have the following vba, which is not giving the desired output. Can u help me correcting the vba. thanks in advance

Well:
By, simply stating that your code "doesn't work," along with code that appears to have had very little if any troubleshooting performed, and expecting someone to help doesn't usually result in much of an answer and may result in your thread being deleted. Instead, please tell us what you were expecting to happen, what actually happened.

For any errors: for each error; the EXACT title, error number, and descriptions that occurred and at what line in your posted code the error occurred.

These are the minimum requirements for posting a question of this nature.

[cliffs]

Hi, thanks for your help, Actually, I want the the following result, but I get the wrong result while running the module :

sr.No. zonetype  result     vba result 
                 expected   
  1        1       1130     1136
  2        2       1147     1137
  3        2       1147     1147
  4        2       1149     1147
  5        1       1127     1147
  6        1       1127     1147
  7        1       1127     1147

basically I want to check maximum high before its declining the high for zonetype 2 and minimum low before its start increase the for zonetype 1. In my vba it checks for next price and thereafter it stops. I want to compare the current high with previous high for each sr_no.s, if current high > previous high then I want to check, current high with next high till I get the next high is lower then the current high for zonetype 2 and then move to sr_no.2,3,4 etc and vice versa for zonetype 1.


thanks for help