Parameterized Query Usage

Cremate

Registered User.
Local time
Today, 09:22
Joined
Feb 18, 2008
Messages
25
So I have a query which prompts the user for a username to search by, GetUsername.

Im used to TSQL usage which would be something like:

exec GetUsername 'blah'

My goal is to generate about 10 reports based on this query but with different (hardcoded) input. I do not want to prompt the user while generating these reports.

How would I invoke this query with a hardcoded parameter? (Id rather not save 10-20 versions of it).
 
If the reports are the same other than the data (ie you are reporting on the same fields in the same way), you could create a query that returns the data for all of the Usernames. Then create one report with a new page for each username. This would be pretty simple but would it give you what you want?
 
If the reports are the same other than the data (ie you are reporting on the same fields in the same way), you could create a query that returns the data for all of the Usernames. Then create one report with a new page for each username. This would be pretty simple but would it give you what you want?

about 10 reports with 2 subreports on them. The two subreports make use of the initial query I spoke of. Each report has a unique header as well. The subreport queries are nested and fairly complicated thus without refactoring them they only differ by that one input. (We used to have 20-40 queries, only differing by a hardcoded value, im trying to make it more generic)
 
You could build your query in VBA and save as a querydef or append the data to a table and bind your reports to that. Not sure its going to be any more elegant than copying and pasting the query 20 times and altering the parameter.
 
You could build your query in VBA and save as a querydef or append the data to a table and bind your reports to that. Not sure its going to be any more elegant than copying and pasting the query 20 times and altering the parameter.

Yuck, whyd I agree to work with access? :D

Cheers
 
If the reports are the same other than the data (ie you are reporting on the same fields in the same way), you could create a query that returns the data for all of the Usernames. Then create one report with a new page for each username. This would be pretty simple but would it give you what you want?

The problem with this is that Ill be running a top 10 and bottom 10 for each username's data. Looks like theres not going to be an elegant way to do this....

Any ideas?
 
its only a little bit of programming

generate your queries to return the top10 etc

then process these queries with a recordset

Code:
in pseudocode

set myrecordset = currentdb.openrecordset(top10query)
while not eof(myrecordset)

   myvariable = recordset!importantparameter
   docmd.openreport  {which uses myvariable}

   myrecordset.movenext
wend
 
Ok I think Im still being misunderstood.

Ill demonstrate here:

Report1
Subreport: X
Subreport: Y
Subreport: Query: Top 10, Parameter: "EASTERN"
Subreport: Bottom 10 'EASTERN'

Report2
Subreport: Top 10 'WESTERN'
Subreport: Bottom 10 'WESTERN'

Report3
Subreport: Top 10 'CENTRAL'
Subreport: Bottom 10 'CENTRAL'

etc...

Note there will be other subreports in these reports, but I have designated queries for them already (see X, Y above).

Is there a way to do this besides having a designated query for each subreport, using param queries as I am trying to demonstrate above?


Gemma, The top 10 query is inclusive using the parameter, not after the query has run.


-CREMATE

its only a little bit of programming

generate your queries to return the top10 etc

then process these queries with a recordset

Code:
in pseudocode

set myrecordset = currentdb.openrecordset(top10query)
while not eof(myrecordset)

   myvariable = recordset!importantparameter
   docmd.openreport  {which uses myvariable}

   myrecordset.movenext
wend
 
My intent is to reuse top 10 bottom 10 without having to store it 20 times with a changed WHERE clause.

These queries (which make use of other queries, ie nested) will be run on other tables as well, so the more general I keep them the more beneficial (and reusable) they are.
 
well why not something like this

in your query you have to use a function to interroqate the variable - perhaps thats what you are missing

Code:
function readvar as string
readvar = myvariable
end function

Code:
and the processing loop in pseudocode

loop:
 myvariable= inputbox("enter variable")
      if myvariable = "done" then exit sub
      docmd.openreport "myreport"  {which uses myvariable}
     {if you have code for it, keep this open until finished}
      while isopen("myreport")
           doevents
      wend
goto loop
 

Users who are viewing this thread

Back
Top Bottom