Query with multiple combo box selections

M

MaryMalone

I am having an issue with the combo boxes on my form. I'm not sure what the
easiest way to write the query is, whether its VBA or just on the query
screen.

I have 6 criteria that are being chosen from combo boxes which then
requeries a linked query on a subform. This works just fine. The problem is
that I want the user to be able to choose any of the combo boxes and leave
the ones they dont want to query by blank and return only the results based
on the selected boxes. I believe my issue right now is that I have dont want
to have a ton of lines in the query to set every combination of
possibilities. Below is the SQL statement for the query.

Any tips would be greatly appreciated. Maybe I'm just thinking to hard on
this one and have really confused myself. I use Access all the time so I'm
pretty familiar with what it can do, but I'm not sure the best way to do this
one.

Thanks in advance!
Mary


SELECT qryProjectHeader.Classification, qryProjectHeader.[Super-Category],
qryProjectHeader.Category, qryProjectHeader.Project_Manager,
qryProjectHeader.IL_Status, qryProjectHeader.[Project_#],
qryProjectHeader.Project_Description, qryProjectHeader.Project_Engineer,
qryProjectHeader.MfgQA_Engineer, qryProjectHeader.Project_Type,
qryProjectHeader.Annual_Run_Rate, qryProjectHeader.Status_Notes,
qryProjectHeader.Comments, qryProjectHeader.Labor_Hours,
qryProjectHeader.Expenses, qryProjectHeader.Capital,
qryProjectHeader.Proj_IL1_Date, qryProjectHeader.Proj_IL2_Date,
qryProjectHeader.Proj_IL3_Date, qryProjectHeader.Proj_IL4_Date,
qryProjectHeader.Proj_IL5_Date, qryProjectHeader.Act_IL1_Date,
qryProjectHeader.Act_IL2_Date, qryProjectHeader.Act_IL3_Date,
qryProjectHeader.Act_IL4_Date, qryProjectHeader.Act_IL5_Date,
qryProjectHeader.Project_Division, qryProjectHeader.Monthly_Run_Rate,
qryProjectHeader.Gross_Months_Savings, qryProjectHeader.Net_Months_Savings,
qryProjectHeader.Total_Savings, qryProjectHeader.Inventory_MOH,
qryProjectHeader.Annual_Spend
FROM qryProjectHeader
GROUP BY qryProjectHeader.Classification, qryProjectHeader.[Super-Category],
qryProjectHeader.Category, qryProjectHeader.Project_Manager,
qryProjectHeader.IL_Status, qryProjectHeader.[Project_#],
qryProjectHeader.Project_Description, qryProjectHeader.Project_Engineer,
qryProjectHeader.MfgQA_Engineer, qryProjectHeader.Project_Type,
qryProjectHeader.Annual_Run_Rate, qryProjectHeader.Status_Notes,
qryProjectHeader.Comments, qryProjectHeader.Labor_Hours,
qryProjectHeader.Expenses, qryProjectHeader.Capital,
qryProjectHeader.Proj_IL1_Date, qryProjectHeader.Proj_IL2_Date,
qryProjectHeader.Proj_IL3_Date, qryProjectHeader.Proj_IL4_Date,
qryProjectHeader.Proj_IL5_Date, qryProjectHeader.Act_IL1_Date,
qryProjectHeader.Act_IL2_Date, qryProjectHeader.Act_IL3_Date,
qryProjectHeader.Act_IL4_Date, qryProjectHeader.Act_IL5_Date,
qryProjectHeader.Project_Division, qryProjectHeader.Monthly_Run_Rate,
qryProjectHeader.Gross_Months_Savings, qryProjectHeader.Net_Months_Savings,
qryProjectHeader.Total_Savings, qryProjectHeader.Inventory_MOH,
qryProjectHeader.Annual_Spend
HAVING ((([Forms]![FrmProjectsAnalysis]![ComboClassification]) Is Null) AND
(([Forms]![FrmProjectsAnalysis]![ComboSuperCat]) Is Null) AND
(([Forms]![FrmProjectsAnalysis]![ComboCat]) Is Null) AND
(([Forms]![FrmProjectsAnalysis]![ComboProjMgr]) Is Null) AND
(([Forms]![FrmProjectsAnalysis]![ComboILStatus]) Is Null) AND
(([Forms]![FrmProjectsAnalysis]![ComboProj]) Is Null)) OR
(((qryProjectHeader.Classification)=[Forms]![FrmProjectsAnalysis]![ComboClassification])
AND
((qryProjectHeader.[Super-Category])=[Forms]![FrmProjectsAnalysis]![ComboSuperCat])
AND ((qryProjectHeader.Category)=[Forms]![FrmProjectsAnalysis]![ComboCat])
AND
((qryProjectHeader.Project_Manager)=[Forms]![FrmProjectsAnalysis]![ComboProjMgr])
AND
((qryProjectHeader.IL_Status)=[Forms]![FrmProjectsAnalysis]![ComboILStatus])
AND
((qryProjectHeader.[Project_#])=[Forms]![FrmProjectsAnalysis]![ComboProj]))
OR
(((qryProjectHeader.[Project_#])=[Forms]![FrmProjectsAnalysis]![ComboProj])
AND (([Forms]![FrmProjectsAnalysis]![ComboClassification]) Is Null)) OR
(((qryProjectHeader.[Project_#])=[Forms]![FrmProjectsAnalysis]![ComboProj])
AND (([Forms]![FrmProjectsAnalysis]![ComboSuperCat]) Is Null)) OR
(((qryProjectHeader.[Project_#])=[Forms]![FrmProjectsAnalysis]![ComboProj])
AND (([Forms]![FrmProjectsAnalysis]![ComboCat]) Is Null)) OR
(((qryProjectHeader.[Project_#])=[Forms]![FrmProjectsAnalysis]![ComboProj])
AND (([Forms]![FrmProjectsAnalysis]![ComboProjMgr]) Is Null)) OR
(((qryProjectHeader.[Project_#])=[Forms]![FrmProjectsAnalysis]![ComboProj])
AND (([Forms]![FrmProjectsAnalysis]![ComboILStatus]) Is Null)) OR
(((qryProjectHeader.IL_Status)=[Forms]![FrmProjectsAnalysis]![ComboILStatus])
AND (([Forms]![FrmProjectsAnalysis]![ComboProj]) Is Null)) OR
(((qryProjectHeader.IL_Status)=[Forms]![FrmProjectsAnalysis]![ComboILStatus])
AND (([Forms]![FrmProjectsAnalysis]![ComboProjMgr]) Is Null)) OR
(((qryProjectHeader.IL_Status)=[Forms]![FrmProjectsAnalysis]![ComboILStatus])
AND (([Forms]![FrmProjectsAnalysis]![ComboCat]) Is Null)) OR
(((qryProjectHeader.IL_Status)=[Forms]![FrmProjectsAnalysis]![ComboILStatus])
AND (([Forms]![FrmProjectsAnalysis]![ComboSuperCat]) Is Null)) OR
(((qryProjectHeader.IL_Status)=[Forms]![FrmProjectsAnalysis]![ComboILStatus])
AND (([Forms]![FrmProjectsAnalysis]![ComboClassification]) Is Null)) OR
(((qryProjectHeader.Project_Manager)=[Forms]![FrmProjectsAnalysis]![ComboProjMgr])
AND (([Forms]![FrmProjectsAnalysis]![ComboProj]) Is Null)) OR
(((qryProjectHeader.Project_Manager)=[Forms]![FrmProjectsAnalysis]![ComboProjMgr])
AND (([Forms]![FrmProjectsAnalysis]![ComboILStatus]) Is Null)) OR
(((qryProjectHeader.Project_Manager)=[Forms]![FrmProjectsAnalysis]![ComboProjMgr])
AND (([Forms]![FrmProjectsAnalysis]![ComboCat]) Is Null)) OR
(((qryProjectHeader.Project_Manager)=[Forms]![FrmProjectsAnalysis]![ComboProjMgr])
AND (([Forms]![FrmProjectsAnalysis]![ComboSuperCat]) Is Null)) OR
(((qryProjectHeader.Project_Manager)=[Forms]![FrmProjectsAnalysis]![ComboProjMgr])
AND (([Forms]![FrmProjectsAnalysis]![ComboClassification]) Is Null)) OR
(((qryProjectHeader.[Super-Category])=[Forms]![FrmProjectsAnalysis]![ComboSuperCat])
AND (([Forms]![FrmProjectsAnalysis]![ComboProj]) Is Null)) OR
(((qryProjectHeader.[Super-Category])=[Forms]![FrmProjectsAnalysis]![ComboSuperCat])
AND (([Forms]![FrmProjectsAnalysis]![ComboILStatus]) Is Null)) OR
(((qryProjectHeader.[Super-Category])=[Forms]![FrmProjectsAnalysis]![ComboSuperCat])
AND (([Forms]![FrmProjectsAnalysis]![ComboProjMgr]) Is Null)) OR
(((qryProjectHeader.[Super-Category])=[Forms]![FrmProjectsAnalysis]![ComboSuperCat])
AND (([Forms]![FrmProjectsAnalysis]![ComboCat]) Is Null)) OR
(((qryProjectHeader.[Super-Category])=[Forms]![FrmProjectsAnalysis]![ComboSuperCat])
AND (([Forms]![FrmProjectsAnalysis]![ComboClassification]) Is Null)) OR
(((qryProjectHeader.Category)=[Forms]![FrmProjectsAnalysis]![ComboCat]) AND
(([Forms]![FrmProjectsAnalysis]![ComboProj]) Is Null)) OR
(((qryProjectHeader.Category)=[Forms]![FrmProjectsAnalysis]![ComboCat]) AND
(([Forms]![FrmProjectsAnalysis]![ComboILStatus]) Is Null)) OR
(((qryProjectHeader.Category)=[Forms]![FrmProjectsAnalysis]![ComboCat]) AND
(([Forms]![FrmProjectsAnalysis]![ComboProjMgr]) Is Null)) OR
(((qryProjectHeader.Category)=[Forms]![FrmProjectsAnalysis]![ComboCat]) AND
(([Forms]![FrmProjectsAnalysis]![ComboSuperCat]) Is Null)) OR
(((qryProjectHeader.Category)=[Forms]![FrmProjectsAnalysis]![ComboCat]) AND
(([Forms]![FrmProjectsAnalysis]![ComboClassification]) Is Null)) OR
(((qryProjectHeader.Classification)=[Forms]![FrmProjectsAnalysis]![ComboClassification])
AND (([Forms]![FrmProjectsAnalysis]![ComboProj]) Is Null)) OR
(((qryProjectHeader.Classification)=[Forms]![FrmProjectsAnalysis]![ComboClassification])
AND (([Forms]![FrmProjectsAnalysis]![ComboILStatus]) Is Null)) OR
(((qryProjectHeader.Classification)=[Forms]![FrmProjectsAnalysis]![ComboClassification])
AND (([Forms]![FrmProjectsAnalysis]![ComboProjMgr]) Is Null)) OR
(((qryProjectHeader.Classification)=[Forms]![FrmProjectsAnalysis]![ComboClassification])
AND (([Forms]![FrmProjectsAnalysis]![ComboCat]) Is Null)) OR
(((qryProjectHeader.Classification)=[Forms]![FrmProjectsAnalysis]![ComboClassification]) AND (([Forms]![FrmProjectsAnalysis]![ComboSuperCat]) Is Null));
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sharepoint not capturing staff name when automation runs 0
How to select all months from a table to include in a report 3
Search a query from form 1
Size limit to Query statement 1
sum of two queries 6
Active Content in this file is blocked issue 1
selecting null with an iif expression in criteria access 2k3 13
Is Null 2

Top