Possible to set up a parameter for "Top Values" function?

[Keetso]

Hi.

I'm sure this has been addressed already, but I have been unable to find the answer so please pardon me for asking it again.

I've created a report that lists the top 50 sales people (based on total sales obviously) for a given month. I use the "Top Values" function and it works great but is there a way to set up a parameter to ask the end user how many top sales people to list using the "Top Values" function?

TIA

K

 

[cghoga]

can you show the code you have?

 

[dhookom]

Keetso,
You can't fill in the "top" value with a parameter or reference to a form control. You can modify the SQL of a saved query. Another option is display your results in a report where you can use code to cancel the printing of records beyond a specified number.

Come on back with specific questions.

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

 

[JonFer]

If you have a query that calculates the total sales by sales rep, you can write a query based on the totals one that calculates the rank of each sales rep and has a criteria that prompts for the number to include.

Select SalesRep, TotalSales
From  qryTotal
Where (Select Count(*) from qryTotal as a
       Where a.TotalSales > qryTotal.TotalSales)+1 <= [Enter number of reps]

Link this query to the query driving your report and it will only include the top however many reps.