Solved

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

Posted on 2012-04-09
11
363 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
  • 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
 

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

895 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

14 Experts available now in Live!

Get 1:1 Help Now