query/parameter strategy..

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

nycdon - 06 Jan 2006 19:55 GMT

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

Reply to this Message

Marshall Barton - 06 Jan 2006 21:57 GMT

>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
[quoted text clipped - 6 lines]
>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.

Signature

Marsh
MVP [MS Access]

Reply to this Message

nycdon - 06 Jan 2006 22:36 GMT

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

> >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
[quoted text clipped - 10 lines]
> How about you explain the fields in the table and what
> options you want to provide users in terms of the fields.

Reply to this Message

Marshall Barton - 06 Jan 2006 23:17 GMT

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.

Signature

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.
[quoted text clipped - 29 lines]
>> How about you explain the fields in the table and what
>> options you want to provide users in terms of the fields.

Reply to this Message

nycdon - 07 Jan 2006 01:40 GMT

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.
[quoted text clipped - 56 lines]
> >> How about you explain the fields in the table and what
> >> options you want to provide users in terms of the fields.

Reply to this Message