select top X

S

Scott Shively

Instead of using 'select top 20', I want to 'select top
Forms![Recipient]![Quanity]' where Forms![Recipient]![Quanity] is a user
specified number from my form that launches the query. How would I do this?
SQL burps when I use a variable instead of a constant.

Thank you,

Scott Shively
 
M

Michel Walsh

Hi,

No, not through that syntax, but you can "rank" the data, probably
something like:


SELECT FIRST(a.f1), FIRST(a.f2), FIRST(a.f3)
FROM myTable As a INNER JOIN myTable As b
ON a.compare <= b.compare
GROUP BY a.pk
HAVING COUNT(*) <= [parameter]
ORDER BY compare DESC



with "compare" the field supplying the ordering, and pk the primary key
field. f1, f2, f3 are "generic" fields.


You can use LAST, MIN or MAX, in this case, rather than FIRST.



Hoping it may help,
Vanderghast, Access MVP
 
Top