SQL Query Too Complex

[Access::Student]

Hey, I've run into the fairly common "SQL too complex" error but I'm not sure
qhat I'm doing wrong. My SQL is the following:

SELECT SUBMISSIONS.title, SUBMISSIONS.timing, SUBMISSIONS.activity,
SUBMISSIONS.vote_1, SUBMISSIONS.vote_2, FINANCIALS.*, [Financial
Totals].amount_approved
FROM (SUBMISSIONS INNER JOIN FINANCIALS ON SUBMISSIONS.kp_submissions_id =
FINANCIALS.kf_submissions_id) INNER JOIN [Financial Totals] ON
FINANCIALS.kp_financials_id = [Financial Totals].kp_financials_id
WHERE (
((FINANCIALS.[kf_programs_id]=[Forms]![Reports Launcher]![f_program_filter])
OR ([Forms]![Reports Launcher]![f_program_filter]="ALL"))
AND
((FINANCIALS.[fiscal_year]=[Forms]![Reports Launcher]![f_fiscal_year_filter])
OR ([Forms]![Reports Launcher]![f_fiscal_year_filter]="ALL"))
AND
((FINANCIALS.[time_scale]=[Forms]![Reports Launcher]![f_time_scale_filter])
OR ([Forms]![Reports Launcher]![f_time_scale_filter]="ALL")));

It seems fairly straight forwards, I'm selecting records from 2 Tables and 1
other query, then filtering them based on a few combo boxes. I've used this
technique a few times before and it's always worked. Also, the error only
occurs when I add the WHERE statement, but I can't find any errors in it.

Any help?

 

[Access::Student]

Thanks for the answers, I actually just figured it out, but have run into
another problem.

the problem was that [f_program_filter] pulled it's row source from a table
that did not include the word "ALL", and it is set to limit to list. And
since the value of [f_program_filter] was set to "ALL" by the FormLoad Event,
it was crashing. It works if I change it though.

So what I want to do is keep my SQL intact, but modify the row source so
that it includes "ALL". Is there any way I can select a single column from a
table then append one row to it, all in one query? If not what should I do? I
don't want to actually add a record that has it's title field called "ALL".

Thanks

I'm assuming that the [Reports Launcher] form is open.

Start by removing all the criteria in the WHERE clause. Does it work OK? If
not, something is probably wrong with the joins.

If it does work with the WHERE clause removed, try putting in one criteria
at a time and running it. Keep going until it breaks again.

Another thing to try is hard coding the criteria instead of using the form
to see what works. It could be something with the form.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hey, I've run into the fairly common "SQL too complex" error but I'm not sure
qhat I'm doing wrong. My SQL is the following:

SELECT SUBMISSIONS.title, SUBMISSIONS.timing, SUBMISSIONS.activity,
SUBMISSIONS.vote_1, SUBMISSIONS.vote_2, FINANCIALS.*, [Financial
Totals].amount_approved
FROM (SUBMISSIONS INNER JOIN FINANCIALS ON SUBMISSIONS.kp_submissions_id =
FINANCIALS.kf_submissions_id) INNER JOIN [Financial Totals] ON
FINANCIALS.kp_financials_id = [Financial Totals].kp_financials_id
WHERE (
((FINANCIALS.[kf_programs_id]=[Forms]![Reports Launcher]![f_program_filter])
OR ([Forms]![Reports Launcher]![f_program_filter]="ALL"))
AND
((FINANCIALS.[fiscal_year]=[Forms]![Reports Launcher]![f_fiscal_year_filter])
OR ([Forms]![Reports Launcher]![f_fiscal_year_filter]="ALL"))
AND
((FINANCIALS.[time_scale]=[Forms]![Reports Launcher]![f_time_scale_filter])
OR ([Forms]![Reports Launcher]![f_time_scale_filter]="ALL")));

It seems fairly straight forwards, I'm selecting records from 2 Tables and 1
other query, then filtering them based on a few combo boxes. I've used this
technique a few times before and it's always worked. Also, the error only
occurs when I add the WHERE statement, but I can't find any errors in it.

Any help?

 

[John W. Vinson]

So what I want to do is keep my SQL intact, but modify the row source so
that it includes "ALL". Is there any way I can select a single column from a
table then append one row to it, all in one query?

Yes. Use a UNION query with a one-row query:

SELECT whatever FROM yourtable
UNION ALL
SELECT "ALL", <the same number of lieteral values in total>
FROM yourtable;