TOP 3 in row source

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
 
D

Dale Fye

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
 
R

Robert

Oh, no. I just want it to return 3 records. Maybe I should use UP TO 3
ROWS instead.
 
M

Michel Walsh

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
 
R

Robert

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...
 
Top