Expand|Select|Wrap|Line Numbers
- 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
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
Expand|Select|Wrap|Line Numbers
- 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
Expand|Select|Wrap|Line Numbers
- 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