Private Sub Command6_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fd As ADODB.Field
Dim vsql As String
Dim J As Integer
Dim K As Variant
Dim N As Integer
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn
vsql = "SELECT MARKS.IDNO, MARKS.TEST_CODE, MARKS.SECTION_ID, MARKS.S_R1, MARKS.SUB1, MARKS.S_RES1 FROM MARKS WHERE (((MARKS.TEST_CODE) = 1) And ((MARKS.SECTION_ID) =3) And ((MARKS.S_RES1) = 'P'))ORDER BY MARKS.SUB1 DESC"
rs.Open vsql, cn, adOpenDynamic, adLockOptimistic, adCmdText
J = 1
Do
K(N) = rs.Fields("SUB1")
For N = 1 To 29
If K(N) = K(N + 1) Then
rs.Fields("S_R1") = J
rs.Update
rs.MoveNext
Else
J = J + 1
rs.Fields("S_R1") = J
rs.Update
rs.MoveNext
End If
Next N
Loop Until rs.EOF
MsgBox "RANK PROCESS OVER", vbOKOnly, "RANK"
End Sub
above i wrote a code in access vba editor
i want to do make a rank by order of marks
i select rank method is
ex.10,10,10,20,30,20,40,20,30 like the marks where 1 rank to 40 and 2nd rank to 30 and 3rd rank to 20 ,4th rank 10
please give me suggession me(this code is not giving perfect ranking)