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

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.
cninghmAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
Try this including the quotes

"Company IN (SELECT Company FROM Query1)"
0
cninghmAuthor Commented:
Syntax is correct, results are incorrect.  Still pulling in company that's supposed to be excluded (e.g., not in query1)
0
Jeffrey CoachmanMIS LiasonCommented:
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

cninghmAuthor Commented:
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
cninghmAuthor Commented:
And, yes, query1 gives me all the companies that are supposed to be included.  (It excludes Company 2).
0
cninghmAuthor Commented:
I cannot use a multi-select list box.  There are thousands of companies.
0
mbizupCommented:
Do you have any code in the reports' open events that may be affecting?
0
mbizupCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cninghmAuthor Commented:
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
mbizupCommented:
Sounds good - thanks for following up.
0
cninghmAuthor Commented:
Thanks so much. Worked perfectly!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.