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
|
|||
|
|||
multiple report filters
I am a relative novice to Access, but do have some familiarity with working
with reports and setting up filters. However, how do I set up a report based off of more than one filter? For example, I want a report to show me, based off a form, a series of personnel contact data by company and then by section. I've figured out how to do a single filter, using the following command line code: "([All Sections])=([Forms]![Rolodex]![All Sections])". This code will return all folks and their POC information, by section, based on the section that is currently displayed in my "Rolodex" form. But now I am trying to filter by both section, and by the company, so my report returns a more refined search. Thanks for any assistance... Tony |
#2
|
|||
|
|||
multiple report filters
What you have provided isn't really a command line. I expect it is from the
DoCmd.OpenReport line? If so, you can use something like: Dim strWhere as String strWhere = "1=1 " If Not IsNull(Me.[All Sections]) Then strWhere = strWhere & " And [All Sections] = """ & Me.[All Sections] & """" End If If Not IsNull(Me.cboCompanyID) Then strWhere = strWhere & " And [CompanyID] = " & Me.cboCompanyID End If DoCmd.OpenReport "rptYourReport", acPreview, , strWhere This code assumes All Sections is text and CompanyID is numeric. -- Duane Hookom MS Access MVP -- "Tony" wrote in message ... I am a relative novice to Access, but do have some familiarity with working with reports and setting up filters. However, how do I set up a report based off of more than one filter? For example, I want a report to show me, based off a form, a series of personnel contact data by company and then by section. I've figured out how to do a single filter, using the following command line code: "([All Sections])=([Forms]![Rolodex]![All Sections])". This code will return all folks and their POC information, by section, based on the section that is currently displayed in my "Rolodex" form. But now I am trying to filter by both section, and by the company, so my report returns a more refined search. Thanks for any assistance... Tony |
#3
|
|||
|
|||
multiple report filters
Try creating a QUERY that uses your criteria and returns the records that
you want, then base your REPORT on that query. -Ed "Tony" wrote in message ... I am a relative novice to Access, but do have some familiarity with working with reports and setting up filters. However, how do I set up a report based off of more than one filter? For example, I want a report to show me, based off a form, a series of personnel contact data by company and then by section. I've figured out how to do a single filter, using the following command line code: "([All Sections])=([Forms]![Rolodex]![All Sections])". This code will return all folks and their POC information, by section, based on the section that is currently displayed in my "Rolodex" form. But now I am trying to filter by both section, and by the company, so my report returns a more refined search. Thanks for any assistance... Tony |
#4
|
|||
|
|||
multiple report filters
I think what you are looking for is Grouping and Sorting. You can set it up
to break and give you subtotals on section and/or company. "Tony" wrote: I am a relative novice to Access, but do have some familiarity with working with reports and setting up filters. However, how do I set up a report based off of more than one filter? For example, I want a report to show me, based off a form, a series of personnel contact data by company and then by section. I've figured out how to do a single filter, using the following command line code: "([All Sections])=([Forms]![Rolodex]![All Sections])". This code will return all folks and their POC information, by section, based on the section that is currently displayed in my "Rolodex" form. But now I am trying to filter by both section, and by the company, so my report returns a more refined search. Thanks for any assistance... Tony |
#5
|
|||
|
|||
multiple report filters
Thank you, All
I “experimented” a bit with all of your suggestions, but sadly with no joy... What I am trying to set up is an automated report based off of my Rolodex form. Essentially, the Rolodex form is on the screen, with several records; all different and distinct with separate companies - much like what you normally see. Let's say the first record has "Adobe" as a company name. On the bottom of this Rolodex form I have set up a button whereby when a user pushes it, the resulting report only displays those records that are listed under the "Adobe" company name, based off of the filter I set up in its properties menu, under the "Filter" line. Basically, a user can see on the report all point of contacts but only in the company named "Adobe". I am trying to take this process one step further by including a sub-section of the company. For instance, for the same company, "Adobe", I have several records and/or people that apply to the "Customer Relations" section of this organization. Ideally, I would like to use another command button on the Rolodex form, where a user pushes it and the resulting report will only display those people in the "Customer Relations" section of "Adobe". Again, all of this is based off of the Rolodex form. I am trying to cut down on forcing any additional steps on the user; but if there is no way around this, then please let me know. Whatever company is currently displayed on the Rolodex form, the command buttons are available to print that company's POC's, either all of them, or by the company's individual section. Thanks again, Tony "Tony" wrote: I am a relative novice to Access, but do have some familiarity with working with reports and setting up filters. However, how do I set up a report based off of more than one filter? For example, I want a report to show me, based off a form, a series of personnel contact data by company and then by section. I've figured out how to do a single filter, using the following command line code: "([All Sections])=([Forms]![Rolodex]![All Sections])". This code will return all folks and their POC information, by section, based on the section that is currently displayed in my "Rolodex" form. But now I am trying to filter by both section, and by the company, so my report returns a more refined search. Thanks for any assistance... Tony |
#6
|
|||
|
|||
multiple report filters
Does your current command button have some code you could share with us?
What is the name of the control bound to the value Customer Relations? Can we assume that both of these values are text? -- Duane Hookom MS Access MVP -- "Tony" wrote in message ... Thank you, All I "experimented" a bit with all of your suggestions, but sadly with no joy... What I am trying to set up is an automated report based off of my Rolodex form. Essentially, the Rolodex form is on the screen, with several records; all different and distinct with separate companies - much like what you normally see. Let's say the first record has "Adobe" as a company name. On the bottom of this Rolodex form I have set up a button whereby when a user pushes it, the resulting report only displays those records that are listed under the "Adobe" company name, based off of the filter I set up in its properties menu, under the "Filter" line. Basically, a user can see on the report all point of contacts but only in the company named "Adobe". I am trying to take this process one step further by including a sub-section of the company. For instance, for the same company, "Adobe", I have several records and/or people that apply to the "Customer Relations" section of this organization. Ideally, I would like to use another command button on the Rolodex form, where a user pushes it and the resulting report will only display those people in the "Customer Relations" section of "Adobe". Again, all of this is based off of the Rolodex form. I am trying to cut down on forcing any additional steps on the user; but if there is no way around this, then please let me know. Whatever company is currently displayed on the Rolodex form, the command buttons are available to print that company's POC's, either all of them, or by the company's individual section. Thanks again, Tony "Tony" wrote: I am a relative novice to Access, but do have some familiarity with working with reports and setting up filters. However, how do I set up a report based off of more than one filter? For example, I want a report to show me, based off a form, a series of personnel contact data by company and then by section. I've figured out how to do a single filter, using the following command line code: "([All Sections])=([Forms]![Rolodex]![All Sections])". This code will return all folks and their POC information, by section, based on the section that is currently displayed in my "Rolodex" form. But now I am trying to filter by both section, and by the company, so my report returns a more refined search. Thanks for any assistance... Tony |
#7
|
|||
|
|||
multiple report filters
Well, my command button is actually a macro I entitled "Rolodex POC's by
Unit". Once a user clicks it, the macro automatically defers to my report called "Rolodex - By Unit". The macro itself doesn't contain the code, but the report does - under the properties menu for the report, on the "Filter" line, the following argument is written: "([All AOR's]=[Forms]![Rolodex]![All AOR's])". As you may already know, this forces the report to open up using only one parameter - the Unit entry on the report must match the Unit entry on the "Rolodex" form. This macro works fine, but now I am trying to create another macro command button that forces another report to open up based on both the AOR and the Section. I've tried several different variations of the argument listed above in an attempt to derive some form of (AND) function, but to no avail. I used "Customer Relations" just as an example. The value is actually entitled "All Sections", bound to the Rolodex table field of the same name. Yes, all values are text Thanks again Tony "Duane Hookom" wrote: Does your current command button have some code you could share with us? What is the name of the control bound to the value Customer Relations? Can we assume that both of these values are text? -- Duane Hookom MS Access MVP -- "Tony" wrote in message ... Thank you, All I "experimented" a bit with all of your suggestions, but sadly with no joy... What I am trying to set up is an automated report based off of my Rolodex form. Essentially, the Rolodex form is on the screen, with several records; all different and distinct with separate companies - much like what you normally see. Let's say the first record has "Adobe" as a company name. On the bottom of this Rolodex form I have set up a button whereby when a user pushes it, the resulting report only displays those records that are listed under the "Adobe" company name, based off of the filter I set up in its properties menu, under the "Filter" line. Basically, a user can see on the report all point of contacts but only in the company named "Adobe". I am trying to take this process one step further by including a sub-section of the company. For instance, for the same company, "Adobe", I have several records and/or people that apply to the "Customer Relations" section of this organization. Ideally, I would like to use another command button on the Rolodex form, where a user pushes it and the resulting report will only display those people in the "Customer Relations" section of "Adobe". Again, all of this is based off of the Rolodex form. I am trying to cut down on forcing any additional steps on the user; but if there is no way around this, then please let me know. Whatever company is currently displayed on the Rolodex form, the command buttons are available to print that company's POC's, either all of them, or by the company's individual section. Thanks again, Tony "Tony" wrote: I am a relative novice to Access, but do have some familiarity with working with reports and setting up filters. However, how do I set up a report based off of more than one filter? For example, I want a report to show me, based off a form, a series of personnel contact data by company and then by section. I've figured out how to do a single filter, using the following command line code: "([All Sections])=([Forms]![Rolodex]![All Sections])". This code will return all folks and their POC information, by section, based on the section that is currently displayed in my "Rolodex" form. But now I am trying to filter by both section, and by the company, so my report returns a more refined search. Thanks for any assistance... Tony |
#8
|
|||
|
|||
multiple report filters
Use the command button wizard to write the code to open your report.
Add this to your code: Dim strWhere as String Dim strReport as String strReport = "Rolodex - By Unit" strWhere = "[All AOR's]=""" & Me.[All AOR's] Y """ " strWhere = strWhere & " AND [All Sections] = """ & [All Sections] & """" DoCmd.OpenReport strReport, acPreview, , strWhere -- Duane Hookom MS Access MVP -- "Tony" wrote in message ... Well, my command button is actually a macro I entitled "Rolodex POC's by Unit". Once a user clicks it, the macro automatically defers to my report called "Rolodex - By Unit". The macro itself doesn't contain the code, but the report does - under the properties menu for the report, on the "Filter" line, the following argument is written: "([All AOR's]=[Forms]![Rolodex]![All AOR's])". As you may already know, this forces the report to open up using only one parameter - the Unit entry on the report must match the Unit entry on the "Rolodex" form. This macro works fine, but now I am trying to create another macro command button that forces another report to open up based on both the AOR and the Section. I've tried several different variations of the argument listed above in an attempt to derive some form of (AND) function, but to no avail. I used "Customer Relations" just as an example. The value is actually entitled "All Sections", bound to the Rolodex table field of the same name. Yes, all values are text Thanks again Tony "Duane Hookom" wrote: Does your current command button have some code you could share with us? What is the name of the control bound to the value Customer Relations? Can we assume that both of these values are text? -- Duane Hookom MS Access MVP -- "Tony" wrote in message ... Thank you, All I "experimented" a bit with all of your suggestions, but sadly with no joy... What I am trying to set up is an automated report based off of my Rolodex form. Essentially, the Rolodex form is on the screen, with several records; all different and distinct with separate companies - much like what you normally see. Let's say the first record has "Adobe" as a company name. On the bottom of this Rolodex form I have set up a button whereby when a user pushes it, the resulting report only displays those records that are listed under the "Adobe" company name, based off of the filter I set up in its properties menu, under the "Filter" line. Basically, a user can see on the report all point of contacts but only in the company named "Adobe". I am trying to take this process one step further by including a sub-section of the company. For instance, for the same company, "Adobe", I have several records and/or people that apply to the "Customer Relations" section of this organization. Ideally, I would like to use another command button on the Rolodex form, where a user pushes it and the resulting report will only display those people in the "Customer Relations" section of "Adobe". Again, all of this is based off of the Rolodex form. I am trying to cut down on forcing any additional steps on the user; but if there is no way around this, then please let me know. Whatever company is currently displayed on the Rolodex form, the command buttons are available to print that company's POC's, either all of them, or by the company's individual section. Thanks again, Tony "Tony" wrote: I am a relative novice to Access, but do have some familiarity with working with reports and setting up filters. However, how do I set up a report based off of more than one filter? For example, I want a report to show me, based off a form, a series of personnel contact data by company and then by section. I've figured out how to do a single filter, using the following command line code: "([All Sections])=([Forms]![Rolodex]![All Sections])". This code will return all folks and their POC information, by section, based on the section that is currently displayed in my "Rolodex" form. But now I am trying to filter by both section, and by the company, so my report returns a more refined search. Thanks for any assistance... Tony |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to run a report for multiple selections? | Amit | Setting Up & Running Reports | 0 | February 9th, 2005 07:25 PM |
Help!! I'm running around in circles! | CathyA | New Users | 19 | December 12th, 2004 07:50 PM |
Still Hoping for help with a Query problem | Don Sealer | Using Forms | 15 | November 13th, 2004 06:24 AM |
Save Report With CreateReport Coding Issue | Jeff Conrad | Setting Up & Running Reports | 8 | July 12th, 2004 08:39 AM |
Label | SRIT | General Discussion | 2 | June 22nd, 2004 09:42 PM |