Parameter Query

[Lumpy3]

I am running a report off a parameter query (Access 2000).
The query has multiple tables with relationships. Depending on which records I need for the report, i need to use various fields(columns) from one of the tables in addition to records from the other tables. How can I pass my desired table field into the design grid of the query on the fly. Is this possible? Thanks for any help.

 

[JimHorton]

Why not just have them all there in the first place? They don't hurt much just being there and not being used--any perceived performance benefit is not worth trying to add or remove fields from a query of a fixed report. Now, if this if for different reports, or if different tables and joins, etc. are used each time, that's another story. You'll have to build the sql on the fly in that case. Could you give some more specifics/
--Jim

 

[Lumpy3]

Thanks for responding Jim. I guess the best way to summarize my question is how do I allow my database user to modify the design of the query in addition to the criteria? They will be entering criteria in the parameter query to specify what records they want to use for the report, but they must also select from a number of fields from a table in the designing grid for a control in the report. I was hoping to allow the user to change the query design in addition to criteria on the fly and not have to generate multiple queries/reports. Thanks again.

 

[JimHorton]

Lumpy,
This is quite possible, just create the sql on the fly prior to opening the report, ie
on a "open report" button click event:

dim sq as string, qd as querydef
set qd = currentdb.querydefs("Name of query that is report source&quot

sq = "Select blah blah"
qd.sql = sq
docmd.openreport "some report"

The exact logic to create the sql string can get as simple or as complex as you want.
You need to define that however, but the possibilities are virtually endless--many sql-generation procedures made by developers consist of hundreds of lines of code involving every type of logic available in vba, based upon whatever criteria you allow the user.
--Jim