Filter Report

Posted on 2011-09-29
Last Modified: 2012-05-12
I am looking to copy a report i had created and apply filters to each new report. I have 2 fields "Division" and "Organization". I would like to create the reports as follows by applying the filter
Division 1 Organization 1
Division 1 Organization 2
Division 2 Organization 1

I have completed alot of work on the final report and would like to keep away from redoing queries and creating new reports.
Looking for instructions on setting the filter on a report and the expression that will yiled the results above.
Question by:SMP319
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
LVL 42

Expert Comment

ID: 36815552
In an absolute sense, it looks like this:
docmd.openreport "YOURREPORTNAME",,,"Division = 1 and Organization = 1"

However, if you are selecting division and Organization from dropdowns on a form before running the report then you can do it like this:

docmd.openreport "YOURREPORTNAME",,,"Division = " &  me.comboDivision  & " and Organization = " & me.comboOrganization


Author Comment

ID: 36815830
cant seem to get it to work, can you provide details on how and where to apply the code to the report for your first option
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36816100

<cant seem to get it to work>
Then can you post What you tried?

Actually it looks like you need "Or" not "And":

Something like this:
"(Division= 1 Or Division=2) Or (Organization =1 Or Organization =2)"

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

LVL 42

Expert Comment

ID: 36816188
I was thinking you wanted to open the report programmatically, like from a macro or from a form (as opposed to opening it from the database navigation window.  How do you intend to open the report?

Author Comment

ID: 36816367
i planned to open from the navigation  window

Author Comment

ID: 36816957
Here is a screen shot. I am new to access. i was trying to add the filter to the report to just show a certain division and Organization in the report.
LVL 42

Accepted Solution

dqmq earned 500 total points
ID: 36817168
OK, I see your confustion now.  Picture = 1000 words :>)

In your example, copy/Paste this into the report's FILTER Property:

      "Division = ""Research"" and Organization = ""IT"""

But, with that approach you need a different report for each filter.  So, then, if you modify the report, you need to make the same changes in all the similar reports.

Another approach uses only one report, but applies the different filters when you run it.  That's the direction I was steering you.  To keep it simple now, let's run the report from a macro.  (Later, you may want to run the report under the control of a form, which is even more powerful).  But for now...

create a macro,  naming it something like "MyReportFor_IT-Research"
   Action:  OpenReport
   ReportName:  MyReportFor_IT-Research
   Filter Name:  blank
   Where Condition:  [Division] = "Reserach" and [Organization] = "IT"
Note, in this case the "filter is going in the Where condition.  Filter name is for a stored filter, which would also work, but is a little more effort.  Also, you will want to blank out the filter property from the report properties.

Now, run the macro from the DB nav window instead of running the report.  



Author Closing Comment

ID: 36817592
So instead of crfeating multiple reports i will create multiple macros to show my results. I am interested in understanding how to do this using the form as you had mentioned. I will post another question. Thanks for you help
LVL 42

Expert Comment

ID: 36818977
Yes. Multiple macros instead of multiple reports.


Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question