Specify Filter criteria before running report

[Steve]

I have a report which shows performance figures for
various groups of products. I have seen examples of
reports where the user could run the same report , but
can select from a list box or combo box to run the
report showing only certain groups or select all to show
the full range. Any assistance with this matter and any
other tips on improving my reporting skills would be
greatly appreciated...


Regards


Steve

 

[Nikos Yannacopoulos]

Steve,

The trick is in the query that "feeds" the report. If your report is
currentluy fed (report recordsource, in proper terms) directly from a table,
then make a query that reads the table, and make the report read from the
query.
Assuming you have a form called frmReportParams, with a textbox, list box or
combo box called, say, SelGroup, you would open your query in design view
and set the crirterion under the group field to:

Like Forms![frmReportParam]![SelGroup] & "*"

That way, if a group is selected, then the query, and consequently the
report, will only select records for that group. If no selection is made in
the list/combo, or the text box is empty, then all records will be returned.
Likewise, assuming you have a textbox FromDate and one ToDate, to filter
your report on a date range, you would need a criterion like the following
under a date field:

Between Forms![frmReportParam]![FromDate] And
Forms![frmReportParam]![ToDate]

If you need a multi-select listbox it gets a little more complicated, in
that you would need some code to "read" the selected values and store them
in a temp table in order to filter your query, or more code to do the
filtering in code without the temp table.

I hope these two examples are enough to get you started.

Nikos

 

[Nikos Yannacopoulos]

It is impossible to say based on what you say, you need to provide more
information. Number of records should not be a determining factor on whether
a report runs or not. If I were to risk a guess, I would say it probably has
to do with bad data, so it really depends on whether your criteria pick up
the records containing them or not.

To begin with, try running the query on its own (not through running the
report), to establish whether it runs OK under any circumstances, or it
"locks up" too. This will tell you whether the problem is in the report, or
the query itself.
If it turns out it's the report, run the query independently with the same
parameters that prevented the report from running, and check the records
returned for "weird" data, it might give you a good clue.
Whichever the case, when you are clearer on the problem, make a new post in
the appropriate NG (reports or queries), providing as much detail as
possible.

HTH,
Nikos

Nikos:

I have created a report using a query like your first suggestion but have

a problem running it. If the answer returns more than 2 or 3 records the
report locks up. Any ideas how to fix that?

Steve,

The trick is in the query that "feeds" the report. If your report is
currentluy fed (report recordsource, in proper terms) directly from a table,
then make a query that reads the table, and make the report read from the
query.
Assuming you have a form called frmReportParams, with a textbox, list box or
combo box called, say, SelGroup, you would open your query in design view
and set the crirterion under the group field to:

Like Forms![frmReportParam]![SelGroup] & "*"

That way, if a group is selected, then the query, and consequently the
report, will only select records for that group. If no selection is made in
the list/combo, or the text box is empty, then all records will be returned.
Likewise, assuming you have a textbox FromDate and one ToDate, to filter
your report on a date range, you would need a criterion like the following
under a date field:

Between Forms![frmReportParam]![FromDate] And
Forms![frmReportParam]![ToDate]

If you need a multi-select listbox it gets a little more complicated, in
that you would need some code to "read" the selected values and store them
in a temp table in order to filter your query, or more code to do the
filtering in code without the temp table.

I hope these two examples are enough to get you started.

Nikos

I have a report which shows performance figures for
various groups of products. I have seen examples of
reports where the user could run the same report , but
can select from a list box or combo box to run the
report showing only certain groups or select all to show
the full range. Any assistance with this matter and any
other tips on improving my reporting skills would be
greatly appreciated...


Regards


Steve

 

[Guest]

Thanks for the response. I have tested the query independently and it works correctly and there is no "weird" data. Plus, I use the same query as the record source for a form and it works fine. I did post my question in Reports with what I hope is enough data but have not received any responses yet, but it's only been a day. I'll keep checking. If you have any other ideas, I would love to hear them. Thanks again!

It is impossible to say based on what you say, you need to provide more
information. Number of records should not be a determining factor on whether
a report runs or not. If I were to risk a guess, I would say it probably has
to do with bad data, so it really depends on whether your criteria pick up
the records containing them or not.

To begin with, try running the query on its own (not through running the
report), to establish whether it runs OK under any circumstances, or it
"locks up" too. This will tell you whether the problem is in the report, or
the query itself.
If it turns out it's the report, run the query independently with the same
parameters that prevented the report from running, and check the records
returned for "weird" data, it might give you a good clue.
Whichever the case, when you are clearer on the problem, make a new post in
the appropriate NG (reports or queries), providing as much detail as
possible.

HTH,
Nikos

Nikos:

I have created a report using a query like your first suggestion but have

a problem running it. If the answer returns more than 2 or 3 records the
report locks up. Any ideas how to fix that?

Steve,

The trick is in the query that "feeds" the report. If your report is
currentluy fed (report recordsource, in proper terms) directly from a table,
then make a query that reads the table, and make the report read from the
query.
Assuming you have a form called frmReportParams, with a textbox, list box or
combo box called, say, SelGroup, you would open your query in design view
and set the crirterion under the group field to:

Like Forms![frmReportParam]![SelGroup] & "*"

That way, if a group is selected, then the query, and consequently the
report, will only select records for that group. If no selection is made in
the list/combo, or the text box is empty, then all records will be returned.
Likewise, assuming you have a textbox FromDate and one ToDate, to filter
your report on a date range, you would need a criterion like the following
under a date field:

Between Forms![frmReportParam]![FromDate] And
Forms![frmReportParam]![ToDate]

If you need a multi-select listbox it gets a little more complicated, in
that you would need some code to "read" the selected values and store them
in a temp table in order to filter your query, or more code to do the
filtering in code without the temp table.

I hope these two examples are enough to get you started.

Nikos

I have a report which shows performance figures for
various groups of products. I have seen examples of
reports where the user could run the same report , but
can select from a list box or combo box to run the
report showing only certain groups or select all to show
the full range. Any assistance with this matter and any
other tips on improving my reporting skills would be
greatly appreciated...


Regards


Steve