select top X

  • Thread starter Thread starter Scott Shively
  • Start date Start date
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
 
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
 
Back
Top