Solved

filter report based on results IN a query (vba or report filter)

Posted on 2012-04-09
11
369 Views
Last Modified: 2012-04-12
Hi Experts:
Is it possible to filter a report based on the results IN a query?

I have a report based on a table that has say 10 companies.  One of those companies has an "Exclude from Printing" flag set on the master company table.  So, I query the MasterCompany table, where "Exclude from Printing" <> True.  I'll call this query Query1

Then when the report is requested, I want to print only the companies that appear in the results of Query1.
I can't get the syntax working:  
 DoCmd.OpenReport MyReport, acPreview, [Company] in,'"Query1"'
Thanks.
0
Comment
Question by:cninghm
[X]
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
  • 6
  • 4
11 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 37825376
Try this including the quotes

"Company IN (SELECT Company FROM Query1)"
0
 

Author Comment

by:cninghm
ID: 37825412
Syntax is correct, results are incorrect.  Still pulling in company that's supposed to be excluded (e.g., not in query1)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37825427
I'm confused...?

Does the "IN" query (run by itself) give you what you want?
If so, then use the IN query as the source for the report.

Or do this as the code that opens the report
DoCmd.OpenReport "YourReport", acViewReport, , "[Exclude from Printing]<>" & True

Or
DoCmd.OpenReport "YourReport", acViewReport,"Query1"

But if it was me, I would just use a multiselct listbox:
http://access.mvps.org/access/reports/rpt0005.htm
simple sample here too;
http://filedb.experts-exchange.com/incoming/2011/12_w50/529693/Access-EEQ23695043MultiSelectLis.mdb

JeffCoachman
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:cninghm
ID: 37825458
It's a rather complex report based on a table that has gone through a bunch of processing.
The table contains values for each company:
Company1  $ $ $ $
Company2  $ $ $ $
Company3  $ $ $ $
Company4  $ $ $ $

On the Company master file, the users want to flip a switch that will control whether or not to print the information for a given company.  So at run time of the report, I want to query the Company master to get all the company codes that should be included on the report.
e.g., if Company Master.Switch = "Y" for Company 2, then my report should only show data for Company 1, 3 and 4.

If they change their minds, and flip the switch back so the company will print, they don't want to re-process the entire stream to produce the table that the report is based off of. That's why the switch isn't interogated at the onset.
0
 

Author Comment

by:cninghm
ID: 37825464
And, yes, query1 gives me all the companies that are supposed to be included.  (It excludes Company 2).
0
 

Author Comment

by:cninghm
ID: 37825466
I cannot use a multi-select list box.  There are thousands of companies.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37825532
Do you have any code in the reports' open events that may be affecting?
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 37825764
I may have confused things by not posting the full syntax earlier (I was using a cellphone).  To clarify, the syntax I posted is for the  WhereCondition parameter of the OpenReport statement.

This is the full syntax:

 
DoCmd.OpenReport MyReport, acPreview, , "Company IN (SELECT Company FROM Query1)"

Open in new window

Alternatively:

 DoCmd.OpenReport MyReport, acPreview, WhereCondition:="Company IN (SELECT Company FROM Query1)"

Open in new window


(Make sure that Company is the correct field name as seen in query1)
0
 

Author Comment

by:cninghm
ID: 37825896
Just "Filter on Load" is set to yes.
I appreciate you typing from a cell phone (feel your pain), and coming back to give full-blown syntax.  I have moved on to another perplexing problem, nailed that one, and will return to this one tomorrow.  I have nothing left right now.  Thanks, and have a good evening.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37826120
Sounds good - thanks for following up.
0
 

Author Closing Comment

by:cninghm
ID: 37838143
Thanks so much. Worked perfectly!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

623 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