Selecting Top 5 Clients 1

[davidchipman]

Hi All,
Hope somebody can help...
I have a report created and I am trying to display the top 5 clients for each employee in terms of billings...the billings is a sum of multiple billings throughout the year...when i do the select top 5, it gives me only the top 5 clients in the entire database, and not the top 5 clients for each employeee...any suggestions...thanks

 

[dhookom]

Sort and group your report by employee then client (based on total billings desc). Add a text box to the detail section of the report
Name: txtRank
Control Source: =1
Running Sum: Over Group
Add code to the On Format event of the detail section:
Cancel = Me.txtRank >5

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[davidchipman]

Hi Duane
I entered this and i get an error saying the following

"The expression you entered on format as the event property setting produced the following error:
Invalid outside procedure"
not sure what to try ...

 

[dhookom]

Sorry, I often forget that not everyone works in code as much as some of us old veterans.

You don't enter the code into the event property. You must set the property to "[Event Procedure]" and then open the code window which will look something like this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

End Sub

Add:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Cancel = Me.txtRank >5
End Sub

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[davidchipman]

Hey Duane
That is what i had done and that was the error i got when i tried to view the report...
any suggestions...thanks again
dave

 

[dhookom]

Your error message looks like you have something in the On Format property other than [Event Procedure].

Duane
MS Access MVP
Find out how to get great answers faq219-2884.