query/parameter strategy..

[Guest]

I have acc2000 mdb, with 10 different reports the user would like to be able
to select from 1 to 4 grouping levels. They want to run for either year 1, or
year 2 and 3, or all years, or 3 years, etc.

wondering if easier way then creating parameter queries for all possible
selection combinations, (multiplied by number of reports, likely like 30 or
so more additional queries).

or bring each report query into vba and alter parameters there based on
selection(s).., then set recordsource to query..

or otherwise..?

thanks!
Don

 

[Marshall Barton]

I have acc2000 mdb, with 10 different reports the user would like to be able
to select from 1 to 4 grouping levels. They want to run for either year 1, or
year 2 and 3, or all years, or 3 years, etc.

wondering if easier way then creating parameter queries for all possible
selection combinations, (multiplied by number of reports, likely like 30 or
so more additional queries).

or bring each report query into vba and alter parameters there based on
selection(s).., then set recordsource to query..

Not sure that even sounds like a query parameter situation.
How about you explain the fields in the table and what
options you want to provide users in terms of the fields.

 

[Guest]

The table related to the selection contains field Grantyear, which could be 1
of 4 values. Currently, the 10 various reports sum across all the Grants.
They'd like to pick which Grant , or combos of grants (1-2, 123, 1-3, 1234,
etc).

Wondering if need to build for each set of existing 10 report queries,
additional sets to handle receiving from 1 to 3 parameters, depending on
their selection(s).

In other words, for report1 of 10... 3 queries to receive if they pick 1, or
2, or 3 (pick all 4 already setup).

10x3 = 30 queries...wondering if another way...or building qdefs in vba was
other option i thought of..

(also wondered if i could pass defined queries the entire 'where' clause as
a variable, or if can only pass the field(s) contained in the 'where'
clause.. )

thanks,
don

 

[Marshall Barton]

That's still a little vague, but it sounds like all this is
about how to run a report with a dynamic Where clause.

If so, then the easiest way to provide this capability is to
base each report an a table/query with no criteria. Then
filter the reports by using a form with text boxes for users
to specify the values they want to see in the report along
with a button to open each report.

The general idea is that the button's Click event procedure
would use the OpenReport method's WhereCondition argument to
filter the report's data. Here's an outline of the kind of
code you would use behind the buttons.

Dim strWhere As String
strWhere = "Grant IN(" & txtGrants & ")"
DoCmd.OpenReport "nameofreport", acviewPreview, _
WhereCondition:= strWhere

I 'm sure it's not that simple, especially if the Grant
field in the table is not a numeric field. If you need
further assistance, please provide more (and more specific)
details.

You haven't explained what these 10 reports are doing. If
they are nearly the same, you may (or may not) be able to
have only one or a few actual reports.

 

[Guest]

Thanks Marsh..the dynamic Where clause was the ticket..

(I'm extracting the selected text field(s) from List box, and building Where
clause accordingly. working well)

thanks again!
Don

That's still a little vague, but it sounds like all this is
about how to run a report with a dynamic Where clause.

If so, then the easiest way to provide this capability is to
base each report an a table/query with no criteria. Then
filter the reports by using a form with text boxes for users
to specify the values they want to see in the report along
with a button to open each report.

The general idea is that the button's Click event procedure
would use the OpenReport method's WhereCondition argument to
filter the report's data. Here's an outline of the kind of
code you would use behind the buttons.

Dim strWhere As String
strWhere = "Grant IN(" & txtGrants & ")"
DoCmd.OpenReport "nameofreport", acviewPreview, _
WhereCondition:= strWhere

I 'm sure it's not that simple, especially if the Grant
field in the table is not a numeric field. If you need
further assistance, please provide more (and more specific)
details.

You haven't explained what these 10 reports are doing. If
they are nearly the same, you may (or may not) be able to
have only one or a few actual reports.
--
Marsh
MVP [MS Access]

The table related to the selection contains field Grantyear, which could be 1
of 4 values. Currently, the 10 various reports sum across all the Grants.
They'd like to pick which Grant , or combos of grants (1-2, 123, 1-3, 1234,
etc).

Wondering if need to build for each set of existing 10 report queries,
additional sets to handle receiving from 1 to 3 parameters, depending on
their selection(s).

In other words, for report1 of 10... 3 queries to receive if they pick 1, or
2, or 3 (pick all 4 already setup).

10x3 = 30 queries...wondering if another way...or building qdefs in vba was
other option i thought of..

(also wondered if i could pass defined queries the entire 'where' clause as
a variable, or if can only pass the field(s) contained in the 'where'
clause.. )