SELECT TOP N - Can N be variable?

[Tim]

I'm trying to provide some flexibility to a SELECT TOP query. I'd like the
user to be able to pull the TOP 3, TOP 5, TOP 20, or whatever. I tried a
parameter:

SELECT TOP [Enter Number of Returns]...

....but that did not work. I tried putting a form field there:

SELECT TOP Forms!MyForm.txtField

....which also did not work. Short of manipulating the SQL via VBA, is there
some way to make that 'N' value more dynamic?

 

[Allen Browne]

No. JET cannot do that, unless you concatenate the number into a string in
VBA.

 

[Michel Walsh]

Hi,


On the other hand, you can RANK your records (first, second, third) by
comparison of values in one of its field, and limit the computed rank to <=
parameter.


MS SQL Server 2005 allows the use of a variable with the TOP clause, just
put parentheses around it:


SELECT TOP (@n) ...


And in fact, you can use whatever returns an integer between these ( ), like
SELECT TOP (SELECT MAX(... ) ...) ...


Top can also be used in an action query, but then, the parentheses are
always mandatory, even for a constant:

DELETE TOP (1) tableName

These syntaxes involving TOP are new to MS SQL Server 2005 and do not work
for MS SQL Server 2000, neither for JET 4.0 (unless you use a pass-through
query).



Hoping it may help,
Vanderghast, Access MVP

No. JET cannot do that, unless you concatenate the number into a string in
VBA.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm trying to provide some flexibility to a SELECT TOP query. I'd like
the
user to be able to pull the TOP 3, TOP 5, TOP 20, or whatever. I tried a
parameter:

SELECT TOP [Enter Number of Returns]...

...but that did not work. I tried putting a form field there:

SELECT TOP Forms!MyForm.txtField

...which also did not work. Short of manipulating the SQL via VBA, is
there
some way to make that 'N' value more dynamic?