Can I have user input update "Top Values" in Property Sheet?

D

Duck

I have created a Query to filter numbered values greatest>lowest, Is there
any way I can add a prompt to allow the user to input how many of those
records they want to see returned? That way if they only want to see 10
records they enter "10", if they want 40 records...you get the idea.

Thanks.
 
J

John Spencer

Yes there is a way, but it involves using a ranking query to get the relative
Rank of each record and then use the rank compared against the user input to
limit the records.

SELECT A.SomeField
FROM SomeTable as A LEFT JOIN SomeTable As B
ON A.SomeField < B.SomeField
GROUP BY A.SomeField
HAVING 1 + Count(B.SomeField) > [Number of Records]

Your other choice would be to use VBA to build the query string on the fly.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Top