Ranking records in Access report 2

[titanl]

Hi,

Have been trying to rank records according highest points in a report from a crosstab query and have create the module below in vba.

Code
Option Compare Database

Dim lngLastPoints As Long
Dim lngLastRank As Long
Dim lngRankInc As Long

Function RankFunction(lngPoints As Long) As Long

If lngLastPoints = lngPoints Then
RankFunction = lngLastRank
lngRankInc = lngRankInc + 1
lngLastPoints = lngPoints
Else
lngRankInc = lngRankInc + 1
RankFunction = lngRankInc
lngLastRank = lngRankInc
lngLastPoints = lngPoints
End If

End Function

It does rank the records but the problem is that when I try to print the report if ther were 25 records the ranking begins at 26 - 50 and if I close and reopen the report it keeps on incrementing. Any suggestions?

Thanks
titanl

 

[PHV]

You should reset your global variables just before the first record is processed.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ZmrAbdulla]

Reset the counters at the beginning of the Function

like

Function RankFunction(lngPoints As Long) As Long
[COLOR=red]lngLastPoints = 0[/color]
If lngLastPoints = lngPoints Then
   RankFunction = lngLastRank
...

Zameer Abdulla

 

[titanl]

Thanks so much for the prompt reply guys it works!!

You have saved my day. Thanks again!

 

[titanl]

Oopps guys, false hope. The counter indeed is reset to 0 however the ties are overlooked.

eg. Among 5 records with 21, 18, 11, 11, 6 the ranking wrongly reads - 1,2,3,4,5 when it should read 1,2,3,3,5.

Any ideas?

Thanks

 

[ZmrAbdulla]

Post your complete code

Zameer Abdulla

 

[titanl]

The complete code is:

Option Compare Database
Dim lngLastPoints As Long
Dim lngLastRank As Long
Dim lngRankInc As Long

Function RankFunction(lngPoints As Long) As Long

lngLastPoints = 0

If lngLastPoints = lngPoints Then
RankFunction = lngLastRank
lngRankInc = lngRankInc + 1
lngLastPoints = lngPoints
Else
lngRankInc = lngRankInc + 1
RankFunction = lngRankInc
lngLastRank = lngRankInc
lngLastPoints = lngPoints
End If
End Function

Here are some explanation which might be helpful.

This above code "RankFunction" is saved as a module. On my report I have a control field for "Schools" and a control field for "Total of Points". To rank the schools and i've created a text box from which control source is as follows:

=RankFunction([Total of Points])

Thanks

 

[ZmrAbdulla]

see this

http://support.microsoft.com/kb/208946

Zameer Abdulla

 

[titanl]

Thanks ZmrAbdulla.

However, I have already visited that link last week but to no success. I've tried ranking in a query in my database it works cause the query is based on a table.

But I think my difficulty is that the "Total of Points" I'm using is from a crosstab query which takes points from different students from the same school and add them together. I guess the query can't look up the data into itself thus the error.

Plz find below what I tried with a query based on the crosstab query:

Rank: (Select Count(*) from qryRanking_BestSchool_Crosstab Where [Total Of Points] < [qryRanking_BestBestSchool_Crosstab].[Total Of Points])+1

This the result I'm getting:

SchoolName Total Of Points Rank
School1 111 1
School2 102 1
School3 95 1
School4 80 1
School5 75 1
School6 73 1
School7 61 1
School8 48 1
School9 0 1

Maybe I'm missing something.

Thanks for suggestions.

 

[PHV]

Rank: DCount("*","qryRanking_BestSchool_Crosstab","[Total Of Points]<=" & [Total Of Points])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[titanl]

Wow! Thanks PHV, it works magnificently. You really made my day. I had to change the < to > though cause it did the opposite. However just one tiny hiccup...

The code is returning the rank as eg:

Total Of Points Rank
81 1
73 2
54 4
54 4
42 5

When it should be:

Total Of Points Rank
81 1
73 2
54 3
54 3
42 5

Can you be of assistance?

Thanks a million again,

titanl

 

[dhookom]

Try remove the "=" from the comparison and then add 1.

Rank: DCount("*","qryRanking_BestSchool_Crosstab","[Total Of Points]>" & [Total Of Points]) +1

Duane
Hook'D on Access
MS Access MVP

 

[titanl]

Hi dhookom,

The replacement of the "=" by "+1" works perfectly.

Thanks so much for you help.

titanl