multiple report filters

[Tony]

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

 

[Duane Hookom]

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.

 

[Ed Robichaud]

Try creating a QUERY that uses your criteria and returns the records that
you want, then base your REPORT on that query.
-Ed

 

[Klatuu]

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]

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

 

[Duane Hookom]

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
--

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

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

 

[Tony]

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

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
--

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

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

 

[Duane Hookom]

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
--

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

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
--

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


:

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