Parametrized Query with Optional TOP Predicate 
Author Message
 Parametrized Query with Optional TOP Predicate

I want to create a parameterized query in Access 2000 that inputs a
parameter for the TOP predicate. In other words, I want the parameter to
define how many records are returned. For example, the following works fine:

SELECT TOP 5 * FROM Client;

However, I am having trouble getting the following to work:

PARAMETERS [RecordsReturned] Long;
SELECT TOP [RecordsReturned] * FROM Client;

What is the best way to achieve what I'm trying to do without creating an
ad-hoc query at program run-time? Also, if this were instead an SQL Server
Stored Procedure, how would this be easier to accomplish?

--
Thanks in advance,

Jack



Fri, 29 Nov 2002 03:00:00 GMT  
 Parametrized Query with Optional TOP Predicate
parameters are meant to reflect the contents of the WHERE clause, not
TOP, so that approach is probably a dead end. you're going to need to
construct the SQL statement dynamically in order to supply the top
value. it would be the same way in sql server, although you could
construct it in a stored procedure instead of an access query.
Quote:

>I want to create a parameterized query in Access 2000 that inputs a
>parameter for the TOP predicate. In other words, I want the parameter to
>define how many records are returned. For example, the following works fine:

>SELECT TOP 5 * FROM Client;

>However, I am having trouble getting the following to work:

>PARAMETERS [RecordsReturned] Long;
>SELECT TOP [RecordsReturned] * FROM Client;

>What is the best way to achieve what I'm trying to do without creating an
>ad-hoc query at program run-time? Also, if this were instead an SQL Server
>Stored Procedure, how would this be easier to accomplish?



Sat, 30 Nov 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Question on Top X Access Predicate

2. TOP Predicate in VB3

3. Executing parametrized queries in ADODC component

4. SQLBase, ODBC and Parametrized Queries

5. Parametrized create table query

6. Parametrized SQL-queries with ODBC interface

7. Crystal and parametrized queries

8. VB crashe when calling parametrized query within dataEnvironment

9. calling Parametrized Access 2000 query

10. Parametrized Query

11. VB/ADO parametrized queries

12. HELP: Parametrized union queries don't work with Jet 4.0

 

 
Powered by phpBB® Forum Software