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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
mbizupConnect With a Mentor Commented:
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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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

From novice to tech pro — start learning today.