Help with Select Top criteria 1

[Dbless]

I want the user to be able to tell me how many records to display on the report. For example: they may only want the top 100 ship to by gross sales in ascending order this time, but next time maybe they only want to see the top 50. I am running a form that gathers information and displays the report. The problem I'm having is, how do I pass the 100 or 50 or whatever the user wants to see to the query that my report run off of? I have tried putting a textbox on the form and then referencing the textbox in the query "Select Top (textbox value)..."

Anyone have a good idea?

Thanks

 

[Isadore]

Db: In a way you've hit on a very important question, one that I have never seen the answer for, i.e., passing variable criteria to a query object at run time. If you could do this you can reduce the overall number of queries in a project.

In your case you're providing specifically the information in a textbox, so not quite the same thing I am referring to. What seems to be the problem? Does the query not see the textbox? What is the error that you are getting?

 

[scriverb]

You can update your query SQL in code with the following as a guide.

Dim db as DAO.database
Set db = CurrentDB
db.QueryDefs(&quot;qryYourQueryName&quot.SQL = &quot;Select TOP &quot; & Me.TopSelectControl & &quot; < enter the rest . . . of your query SQL here>;&quot;
db.close
DoCmd.OpenReport &quot;rptYourReportName&quot;

The above requires a textbox called TopSelectControl on your form where the User enters the number to be selected in the TOP predicate. The code above then is put behind a command button. Make sure that where you see the bolded RED area you put a space immediately behind the first double-quote mark and then paste the rest of the SQL code.



Bob Scriver

 

[Isadore]

scriv: excellent post.

 

[scriverb]

Isadore: Thank you. I thought you might like that technique after reading your post.

I use this technique all the time. Sometimes update the SQL with the Order By and WHERE clauses depending upon a pick from a combobox. To make it easy I have stored the Order by and Where clauses as a field in the Combobox RowSource. I store them kind of like personal stored procedure in a table with and then after the picks just perform the same type of code as above to update the query to be used by a report that is then called. Works pretty good.

Bob Scriver

 

[Isadore]

scriverb: I've been looking for a long time for a solution to eliminate query dependency (criteria) on a Form object. This may do the trick. I want to run a couple of test and post back. Obviously, if you don't have to &quot;lock down&quot; a particular query object to a particular form you can substantially reduce the number of query objects in a project.

 

[scriverb]

I had an application where I created numerous pass-through queries to an RDBC and they were all run through one query called qryTempQuery. The process had easily 8 queries that were loaded dynamically depending upon the parameters from a form prompting. The processes that were executed were determined by the ACCESS queries that took in the qryTempQuery as its recordset. Here is an example of changing the WHERE clause.

Dim db as DAO.database
db.QueryDef(&quot;qryTempQuery&quot.SQL = Mid$(db.QueryDef(&quot;qryTempQuery&quot.SQL,1,Instr(1,dbQueryDef(&quot;qryTempQuery&quot.SQL,&quot;WHERE &quot-1) & &quot; WHERE ...<criteria sql>;&quot;
db.close

This strips of the Select and From portion of the existing query and cancatenates on a new WHERE clause statement.

Good luck with this.


Bob Scriver