Ranking records

[Lotto]

I have the module below to rank a set of records in a table. My
problem is that it ranks the wrong way, I want the largest number in
the dataset to be number one, instead it looks like the snapshot
below. FYI - the records are sorted before ranking in the order
shown:

Sold To Payer Order No Ref Doc No Amt Pgm ClRank
50576 50576 0054213 200700348699 $2,603.16 DLS 5
50576 50576 0053441 200700348697 $400.00 MDF 4
50576 50576 0052465 200700348695 $322.00 MDF 3
50576 50576 0052464 200700348694 $280.00 MDF 2
50576 50576 0052118 200700259700 $154.56 DLS 1

Code is below:

Function AddCustRank()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Long
Dim sRep As String
Dim sRepCompare As String

Set db = CurrentDb
Set rs = db.OpenRecordset("select * FROM [Sales no1] order by Brk,
[Sold to], Amt")


rs.MoveFirst

'initialize the first record
sRep = rs.Fields("Sold to")

Do While Not rs.EOF
'initialize the rank count for a new rep
i = 1

sRepCompare = rs.Fields("Sold to")
Do While Not rs.EOF And sRepCompare = sRep

rs.Edit
rs.Fields("ClRank").Value = i
rs.Update
rs.MoveNext
i = i + 1
If Not rs.EOF Then sRep = rs.Fields("Sold to")

Loop

Loop

 

[Ken Snell MVP]

Change this code line:

Set rs = db.OpenRecordset("select * FROM [Sales no1] order by Brk,
[Sold to], Amt")


to this:

Set rs = db.OpenRecordset("select * FROM [Sales no1] order by Brk,
[Sold to], Amt DESC")