Solved

Filter Report

Posted on 2011-09-29
9
394 Views
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.
0
Comment
Question by:SMP319
  • 4
  • 4
9 Comments
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
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

0
 
LVL 1

Author Comment

by:SMP319
Comment Utility
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility

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


JeffCoachman
0
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
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?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:SMP319
Comment Utility
i planned to open from the navigation  window
0
 
LVL 1

Author Comment

by:SMP319
Comment Utility
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.
Screen-shot.xlsx
0
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
Comment Utility
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.  

Run

















0
 
LVL 1

Author Closing Comment

by:SMP319
Comment Utility
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
0
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
Yes. Multiple macros instead of multiple reports.

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now