arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Today, 21:48
- Joined
- May 7, 2009
- Messages
- 19,175
From the Query you posted in post#14, create a New Query out of it returning all the columns.
for the New Rank Column, put this Expression:
Copy and paste the function in a Module.
for the New Rank Column, put this Expression:
Code:
Rank: fncRank("YourFirstQueryName", "StaffID", [StaffID])
Copy and paste the function in a Module.
Code:
Public Function fncRank(QueryName As String, FieldName As String, FieldValue As Variant) As Long
Dim Criteria As String
Select Case TypeName(FieldValue)
Case "Integer", "Long", "Double", "Single", "Boolean"
Criteria = FieldName & "=" & FieldValue
Case "Date"
Criteria = FieldName & "=#" & Format(FieldValue, "mm/dd/yyyy") & "#"
Case "String"
Criteria = FieldName & "=""" & FieldValue & """"
Case Else
Exit Function
End Select
With CurrentDb.OpenRecordset(QueryName, dbOpenSnapshot)
If Not (.BOF And .EOF) Then
.FindFirst Criteria
If Not .NoMatch Then
While Not .BOF
fncRank = fncRank + 1
.MovePrevious
Wend
End If
End If
End With
End Function