Rank in a report (1 Viewer)
- Thread starter peik
- Start date
nanscombe
Registered User.
- Local time
- Today, 15:01
- Joined
- Nov 12, 2011
- Messages
- 1,082
You called?
The function getRanking() in the attached database should do what I think you are after as long as the Scores are sorted from largest to smallest.
Thank you!
I have now put it into my database, but I get this message: "Undefined function getRanking in the expression"!!
SELECT Kl1Lær1.Navn, Kl1Lær1.Speilnr, Kl1Lær1.Dommer1kl1, Kl1Lær1.Dommer2kl1, Kl1Lær1.Dommer3kl1, Kl1Lær1.Sum, getRanking([Sum],"Descending") AS Ranking
FROM Kl1Lær1
ORDER BY Kl1Lær1.Sum DESC;
I have now put it into my database, but I get this message: "Undefined function getRanking in the expression"!!
SELECT Kl1Lær1.Navn, Kl1Lær1.Speilnr, Kl1Lær1.Dommer1kl1, Kl1Lær1.Dommer2kl1, Kl1Lær1.Dommer3kl1, Kl1Lær1.Sum, getRanking([Sum],"Descending") AS Ranking
FROM Kl1Lær1
ORDER BY Kl1Lær1.Sum DESC;
nanscombe
Registered User.
- Local time
- Today, 15:01
- Joined
- Nov 12, 2011
- Messages
- 1,082
Did you copy the function getRanking() from the Module in my database to a module in yours?
It is not a built in function, it's actually something that I wrote for you and it needs to be placed into a Module. Here is the code if you want to cut and paste it.
It is not a built in function, it's actually something that I wrote for you and it needs to be placed into a Module. Here is the code if you want to cut and paste it.
Code:
Public Function getRanking(ByVal theScore As Long, Optional theDirection As String = "Desc")
Static oldScore As Long, theRanking As Long, thePosition As Long, usePositionInstead As Boolean
' theScore: These should be a set of sorted scores
' theDirection: are the Scores Ascending or Descending? Default is Descending
theDirection = LCase(Left(theDirection, 1))
If theRanking < 0 Then theRanking = 0
If thePosition < 0 Then thePosition = 0
' If theDirection is Descending and the Score is lower than the previous then goto NoReset
' If theDirection is Ascending and the Score is higher than the previous then goto NoReset
If (theDirection = "d") And (oldScore >= theScore) Then GoTo NoReset
If (theDirection = "a") And (oldScore <= theScore) Then GoTo NoReset
' If theDirection is Descending and the Score is higher than the previous then reset the variables
' If theDirection is Ascending and the Score is lower than the previous then reset the variables
thePosition = 0
theRanking = 0
' Set usePositionInstead = false
usePositionInstead = False
NoReset:
' Update thePosition
thePosition = thePosition + 1
If oldScore <> theScore Then
' If the OldScore is not the same as theScore then
' Make oldScore = theScore
' Update the ranking
oldScore = theScore
' Increment theTarnking
theRanking = theRanking + 1
' If usePositionInstead is true then make theRanking = thePosition instead
If usePositionInstead Then theRanking = thePosition
' Set usePositionInstead = false
usePositionInstead = False
Else
' Don't increment theTarnking
theRanking = theRanking + 0
' Set usePositionInstead = true
usePositionInstead = True
End If
' Set getRanking = theRanking
getRanking = theRanking
End Function
Last edited:
I did, but I was expecting the GhostbustersYou called?
@peik: In the attached I show you two ways of doing it by amending the database nanscombe kindly gave us
nanscombe
Registered User.
- Local time
- Today, 15:01
- Joined
- Nov 12, 2011
- Messages
- 1,082
