Linking Top x Query to a form

[macroapa]

Hi, is there a way to make the Top value a variable linked to a form,
along the lines of:

SELECT TOP Forms!Form1!Text0 Table1.xD
FROM Table1;

 

[Ken Snell]

Only if you build the SQL statement for the query in VBA code, then assign
it to the query or run the statement.

 

[John Spencer]

Select Top N records where N is variable ( a parameter)

One method that can be used is to use a ranking query to assign a number to
the records and then use the ranking to return N records.

'Probably not updatable
SELECT MyTable.*
FROM MyTable INNER JOIN
(
SELECT A.MyField, Count(B.MyField) as TheCount
FROM MyTable as A LEFT JOIN MyTable As B
ON A.MyField < B.MyField
GROUP BY A.MyField
) as Ranking
ON MyTable.MyField = Ranking.MyField
WHERE Ranking.TheCount < [Top How Many Number]

'Probably updatable
SELECT MyTable.*
FROM MyTable
WHERE MyField in
(SELECT A.MyField
FROM MyTable as A LEFT JOIN MyTable As B
ON A.MyField < B.MyField
GROUP BY A.MyField
HAVING Count(B.MyField) < [Top How Many Number])

Or
SELECT a.f1, a.f2, LAST(a.f3)
FROM myTable As a INNER JOIN myTable As b
ON a.f1=b.f1 AND a.f2 <= b.f2
GROUP BY a.f1, a.f2
HAVING COUNT(*) <= [Top How Many Number]

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

 

[a a r o n . k e m p f]

sql server now allows that to be a parameter.. I don't think that
Access/Jet will do that directly, you will have to concatenate the SQL
String, leaving yourself open to SQL injection