well, i have a solution, but it's not without flaws. you can declare a
module level variable to hold the "count", and use that in a function to
increment the count, then call the procedure as a calculated field in the
query, as

Dim intCount As Integer

Public Function isRank(ByVal var As Variant) As Integer

   intCount = intCount + 1
   isRank = intCount

End Function

add a field to the query, as

Ranking: isRank([SomeFieldName])

substitute the name of a field in the query's underlying table; any field
will do, since it's not used in the function - its' purpose is only to force
the function to run for each record.

the flaw in this solution is that if you put the above VBA code in a
standard module, the intCount variable will not be reset to zero unless you
close the database and re-open it. so if you run the query more than once
during a session, the rank will be sequential - but it will NOT start at 1.

a way around this problem is to use a command button on a form to open the
query. then you can either add another command button to "Reset Rank", with
code that sets the variable back to zero, as

   intCount = 0

or you can run the above code in the form's Close event, if you're only
going to run the query once while the form is open.

as i said, a flawed solution. suggest you keep checking this thread for a
day or so; maybe someone else will post something better. btw, suggest you
post future messages as text, not HTML, since some newsreaders can only read
text.

hth

Hi Craig,

You can do it with a subquery, as an alternative to calling a VBA
function. E.g. if the records are sorted on the field Score, something
like this:

 SELECT
   (SELECT COUNT(1)
     FROM MyTable As B
     WHERE B.Score > A.Score) + 1 As Place,
   A.Score,
    A.FirstName,
   A.LastName
 FROM MyTable AS A
 ORDER BY A.Score DESC;

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.