Report Won't Run - Query Is Too Complex

[Slej]

I created a Union Query that prompts for three parameters:
1. Retailer Name
2. Distribution Center
3. Division

When I run the query just as a query, it works perfectly fine but when I try
to run the report based on this query, the three prompts show up as they
should BUT when I click the button to open the report, I get an error message
saying the query is too complex.

I don't understand how that can be if it runs smoothly when the query is run
just as itself and not as the source for the report. I'm guessing there must
be something in the report that is causing this but I can't figure out what
it could be.

A little background: prior to creating the union query, the report ran fine
when the query was a regular Select query and only existing stores were
included. I had to modified the query to include New Stores not just
Existing Stores (the purpose of the query is to create Pick Sheets for
Distribution Centers). The two types of stores (new and existing) CANNOT be
included in one table (long story - trust me on this one!), therefore, I
created a second query to extract New stores and built the Union query to
combine the results with the Existing stores query.

Thank you for any help.

 

[Pete D.]

Read this for some hints to fix problem.

http://support.microsoft.com/kb/103429

 

[Duane Hookom]

IMHO, parameter prompts are never appropriate
http://www.tek-tips.com/faqs.cfm?fid=6763. I would change the parameter
prompts to references to controls on forms. Then you might need to specify
the data types of the parameters.

 

[Pete D.]

Duane makes a good point, also users don't like being prompted three times
when one form could answer all the questions. My answer was to your
specific question, his solution is much more elegant. Pete

 

[Slej]

Pete & Duane,

I probably should have mentioned in my original post (I didn't think it
would matter so I didn't) but the prompts are, in fact, on one form (dialog
box that appears when the user selects this particular report) and they do
reference controls on the form (combo boxes).

I ended up resolving my problem by taking an action in the link Pete
referred me to. The original query was based on one table and one query, so
I did a make table query on the one query to turn the results into a table,
then used that table (so my new query is based on two tables). That worked.

Thank you both for your help.

 

[Ed Morgan]

Thanks, guys and doll, for confirming my suspicions. I've often used make
tables for this complexity issue, but thought it was just a work-around to
get past my lack of knowledge. The link to the ms bit about limitations was
also helpful.

 

[Tony Toews [MVP]]

Thanks, guys and doll, for confirming my suspicions. I've often used make
tables for this complexity issue, but thought it was just a work-around to
get past my lack of knowledge.

I've occasionally had the same problem too and have also used temp
tables. Also sometimes some queries and reports would get real ugly
and take a long time to execute. Doing some intermediate calculations
to a temp table made things much faster as well as tidier.

But also see the TempTables.MDB page at my website which illustrates
how to use a temporary MDB in your app.
http://www.granite.ab.ca/access/temptables.htm
This will help with bloat in either the FE or the BE.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Duane Hookom]

If your prompts came from a dialog form, are you prematurely closing this
dialog form at some point in your code?