TOP 3 in row source

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

Hello

In the row source of a listbox I have SELECT TOP 3 ... but it has no effect
/ displays more than 3 rows. Is TOP not valid in row sources? How can I
get around this?

I am ultimately trying to make the number of rows returned to the listbox to
be variable based on a number which I calculate. I tried entering the
constant 3 and it did not work.

Robert
 
Top three based on what? What is the entire SQL statement for the row
source?

Top will return a distinct number, but if the value you are using to sort on
has matches, then it will return all the records that match the 3rd value as
well. So if the field you are sorting on has values (1, 3, 5, 5, 5, 6),
then it will return 5 records.

HTH
Dale
 
You have to supply a unique ordering, like:

ORDER BY fieldName, primaryKey


So, if there is ex-equo due to fieldName, as in (1, 3, 5, 5, 5, 6), the
primaryKey value will 'break' the equality, between the three 5, as
example, and you will get ONLY 3 values (at most).

If you know MS SQL Server 2000, then consider that JET has always WITH TIES
active.



Hoping it may help,
Vanderghast, Access MVP
 
Thank you.

Michel Walsh said:
You have to supply a unique ordering, like:

ORDER BY fieldName, primaryKey


So, if there is ex-equo due to fieldName, as in (1, 3, 5, 5, 5, 6), the
primaryKey value will 'break' the equality, between the three 5, as
example, and you will get ONLY 3 values (at most).

If you know MS SQL Server 2000, then consider that JET has always WITH
TIES active.



Hoping it may help,
Vanderghast, Access MVP
Click to expand...
 
Back
Top