Parameterized Query Usage

[Cremate]

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).

 

[neileg]

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?

 

[Cremate]

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)

 

[neileg]

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.

 

[Cremate]

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?

Cheers

 

[Cremate]

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?

 

[gemma-the-husky]

its only a little bit of programming

generate your queries to return the top10 etc

then process these queries with a recordset

in pseudocode

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

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

   myrecordset.movenext
wend

 

[Cremate]

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

in pseudocode

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

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

   myrecordset.movenext
wend

 

[Cremate]

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.

 

[gemma-the-husky]

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

function readvar as string
readvar = myvariable
end function

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