If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Specify Filter criteria before running report
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 "Steve" wrote in message ... 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 |
#2
|
|||
|
|||
Specify Filter criteria before running report
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? "Nikos Yannacopoulos" wrote: 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 "Steve" wrote in message ... 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 |
#3
|
|||
|
|||
Specify Filter criteria before running report
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 "D'Lilah" wrote in message ... 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? "Nikos Yannacopoulos" wrote: 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 "Steve" wrote in message ... 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 |
#4
|
|||
|
|||
Specify Filter criteria before running report
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!
"Nikos Yannacopoulos" wrote: 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 "D'Lilah" wrote in message ... 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? "Nikos Yannacopoulos" wrote: 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 "Steve" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|