Displaying a user-defined number of records as a query result.

[wisey]

I need to know how to set top values to a user defined amount. Something keyed in on a form say.

 

[Amiel]

First of all you'll have to provide a unique numeric value for each record, or be prepared to have users tell you that your program returns 101 (or more) records when they only asked for 100. <br><br>Reason for this is that the SQL predecate used to extract specific numbers of records at a time will return matching values (ties).<br><br>It should look like this when embeded in your SQL statement:<br><br>&quot;Select top &quot; & NumericUserValue & &quot; From

&quot;<br><br>You'll have to change the forms RecordSource (Me.Recordsource) to the statement and then requery and refresh the form.<br> <p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br>

 

[Amiel]

I neglected to include that the statement will need an 'Order By' clause to identify the specific assortment of records returned. And you'll have to explicitly exclude 'null' values from the ordered by field. <br><br>If you want to allow continued fetches you can use the value of the last record (by calling MoveLast) in the recordset and use it in the 'where clause'.<br><br>So now the initial fetch looks like this:<br><b>Select top &quot; & NumericUserValue & &quot; From

where [ID] is not null Order By [ID]&quot;</b><br><br>get next set of records with this after calling 'MoveLast':<br><b>Select top &quot; & NumericUserValue & &quot; From

Where [ID] &gt; &quot; & Me![ID] & &quot; and [ID] is not null Order By [ID]&quot;</b><br><br>I <i>think</i> i got it right this time.<br> <p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br>

 

[wisey]

Thanks Amiel,, This sound to me like a stupid question, but can I do this from inside a query or do i need to put this in some VB? I'd really like to do it just inside a query?<br><br><br>cheers wisey