Solved

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

Posted on 2012-04-09
11
366 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
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: 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

856 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