Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-16-2013, 06:54 PM   #1
Stang70Fastback
Newly Registered User
 
Join Date: Dec 2012
Posts: 132
Thanks: 17
Thanked 0 Times in 0 Posts
Stang70Fastback is on a distinguished road
Union query works but will not filter report.

Just as the title suggests, I've got a problem. I have a report with the record source based off of "SOURCEDATA."

Then I've got a union query that merges two other queries, which themselves are based off of the "SOURCEDATA" query. So everything is based off of the same source.

The report, when I pull it up without any filters works fine and shows ALL the data. The union query, when I open it on its own, also works fine and shows exactly what I want it to show. However, when I apply it to the Report, nothing changes. I still see ALL of the data. Interestingly, the intermediate queries that the union compares can be applied to the report and that works and shows just what I would expect.

Is there something special about union queries that they can not be used to filter report data? I don't get any errors, but I just don't get any effect of applying it to the report.

If I try to make the report's record source the union query itself, Access seems to get stuck in some sort of loop that slows everything down significantly and I have to [slowly] set the record source back to something else to get it to stop locking up. Is this any sort of hint as to what my issue is?


Last edited by Stang70Fastback; 02-16-2013 at 07:00 PM.
Stang70Fastback is offline   Reply With Quote
Old 02-17-2013, 10:03 AM   #2
apr pillai
Newly Registered User
 
apr pillai's Avatar
 
Join Date: Jan 2005
Location: India
Posts: 686
Thanks: 3
Thanked 109 Times in 96 Posts
apr pillai will become famous soon enough
Send a message via AIM to apr pillai Send a message via Yahoo to apr pillai Send a message via Skype™ to apr pillai
Re: Union query works but will not filter report.

It looks like the Union Query enters into some infinit loop and make the system hang.

I think, a better approach is to create a Report Table out of the Union Query, by running a make-table query (through a macro) and use the table as Record Source to the Report. The Report also can be opened through the macro in second step in the macro. Let the macro run through a Command Button-Click Event Procedure.

If you would like to know few things about the Union Query then find it from here.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
(Learn MS-Access Tips and Tricks)

All responses are based on Access2003/2007

Last edited by apr pillai; 05-11-2018 at 07:57 AM.
apr pillai is offline   Reply With Quote
Old 02-18-2013, 09:06 PM   #3
Stang70Fastback
Newly Registered User
 
Join Date: Dec 2012
Posts: 132
Thanks: 17
Thanked 0 Times in 0 Posts
Stang70Fastback is on a distinguished road
Re: Union query works but will not filter report.

Would it be possible for you to explain in a bit more detail exactly what you mean by a Make-Table Query? It sounds like you are suggesting I turn the Query into a table and use that data instead. That sound like it might work, but I'm not sure how to go about it.

Thanks for the help!

Stang70Fastback is offline   Reply With Quote
Old 02-28-2013, 06:45 AM   #4
apr pillai
Newly Registered User
 
apr pillai's Avatar
 
Join Date: Jan 2005
Location: India
Posts: 686
Thanks: 3
Thanked 109 Times in 96 Posts
apr pillai will become famous soon enough
Send a message via AIM to apr pillai Send a message via Yahoo to apr pillai Send a message via Skype™ to apr pillai
Re: Union query works but will not filter report.

  1. Create a make-table query using Union Query as Data Source, run it and create the output table, say tblReport.
  2. Design the Report using tblReport as Record Source or if you need further filtering the records from tblReport then create a Select Query and use it as report source.
  3. Open the Report in PrintPreview.

Step 1 & 3 must be repeated every time the report source data changes in the Union Query. Running these two actions manually every time is not advisable.

These two actions (1. opening the make-table Query, 2. Opening the Report) can be put in a macro (sample image of a macro is attached) and run the macro from a Command Button Click Event Procedure on the Form, whenever the report source data changes.

In fact you can put two command buttons on the Form, one for re-running the report through the macro, and another one to open the existing report directly in PrintPreview mode.
Attached Images
File Type: jpg macro_image.jpg (50.8 KB, 55 views)

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
(Learn MS-Access Tips and Tricks)

All responses are based on Access2003/2007
apr pillai is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
UNION ALL query works fine - but "too complex" for a report's record source? qqq Queries 3 11-14-2012 09:00 AM
Query works on one report, but not another bigalpha Queries 22 09-06-2012 03:08 PM
Union query report, parameter prompt? Report filter? Lifeseeker Queries 1 05-12-2011 08:26 PM
Query works, report doesnt foxy Reports 7 04-29-2010 08:46 AM
filter which queries are used in union query shenty Queries 22 06-29-2009 05:16 AM




All times are GMT -8. The time now is 03:58 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World