Dynamically limit query results

[benkingery]

I have a query that I'd like to limit the results to. I've been able to find out that the syntax is SELECT TOP 20 or SELECT TOP 100, but my question is what do I do when I would like my top number to be a dynamic number that I put into a text box on a form?

Anyone help on that one?

 

[DCrake]

To do that you would need to go down the vba route with query defs and re creating the query sql prior to running the query.

David

 

[benkingery]

I figured that would be the case. Any idea on syntax specifically for the part I want to be dynamic?

Place the VBA on the form, right? Not called from a module?

 

[namliam]

dont matter,

Make a query call it "yourotherquery_Default" and dont use any top statement in it...

Now in code add your top... Assuming when executed from within the form

Currentdb.querydefs("Yourotherquery").sql = "Select top " & Me.[TheControlContainingTheLimit] & mid(Currentdb.querydefs("Yourotherquery_Default").sql,7)

 

[benkingery]

Hmmmm. how about if the SQL statement is an UPDATE statement instead of just a SELECT?

BTW, what's the difference between using Docmd.runsql and Currentdb.....?

 

[namliam]

DoCmd by default will show the "You are affecting ... records" message when executing a Update/Delete/Create table statement. Currentdb will not...