TOP solution (1 Viewer)
- Thread starter MCAdude
- Start date
- Local time
- Today, 14:55
- Joined
- Feb 28, 2001
- Messages
- 27,241
I assume you mean, like doing a SELECT TOP n and instead of 'n' being a constant, you want to substitute for it.
You have to understand the context in which that query runs. There are NO global variables and NO places to put intermediate values. So whatever you would put in the place of 'n' has to be something that can be determined from query context.
Now, if you were willing to do something a little bit more complex, you could try this:
Write a function that generates an SQL query on the fly. Either (a) include an InputBox function to get 'n' or (b) write a DLookup or other function to look up 'n' - either one is the same for this purpose.
Build the query. Now open the database and find the QueryDefs collection. Create a new one based on the SQL string you just built.
And if you REALLY are getting fancy-schmancy, you can delete the old one from the QueryDefs collection before you store the new one, so you don't have to go through the process of making up a name.
If this is a query that is run indirectly from existing code (such as after you click a button on a form) then you can run this function from the code before you run the query itself. But if not, you can still run functions from a macro via RunCode action.
You have to understand the context in which that query runs. There are NO global variables and NO places to put intermediate values. So whatever you would put in the place of 'n' has to be something that can be determined from query context.
Now, if you were willing to do something a little bit more complex, you could try this:
Write a function that generates an SQL query on the fly. Either (a) include an InputBox function to get 'n' or (b) write a DLookup or other function to look up 'n' - either one is the same for this purpose.
Build the query. Now open the database and find the QueryDefs collection. Create a new one based on the SQL string you just built.
And if you REALLY are getting fancy-schmancy, you can delete the old one from the QueryDefs collection before you store the new one, so you don't have to go through the process of making up a name.
If this is a query that is run indirectly from existing code (such as after you click a button on a form) then you can run this function from the code before you run the query itself. But if not, you can still run functions from a macro via RunCode action.
Fornatian
Dim Person
- Local time
- Today, 20:55
- Joined
- Sep 1, 2000
- Messages
- 1,396
If you do a search on select top in this forum I have posted an example of this recently showing what Doc_Man is talking about.
Ian
EDIT, TRY HERE:
http://www.access-programmers.co.uk/ubb/Forum1/HTML/007518.html
[This message has been edited by Fornatian (edited 04-03-2002).]
Ian
EDIT, TRY HERE:
http://www.access-programmers.co.uk/ubb/Forum1/HTML/007518.html
[This message has been edited by Fornatian (edited 04-03-2002).]
