Search code examples
vbadatabasems-accessms-access-2013

Querying all the records based on multiple combo boxes from a form, in MS Access

Please help me solve this. I have 2 combo boxes (Function Name & Year). I would like to query the results based on those combo boxes and if those boxes are left empty, all the records to be displayed. I have used the below code and it is working only for 1 combo box:

=[Forms]![YourForm]![YourCombo] OR [Forms]![YourForm]![YourCombo] IS NULL

When I apply the same logic for the 2nd Combobox, the results are not showing up. Below is the code which I have used:

FROM Master_DataBase
WHERE (((Master_DataBase.Status_of_Project)="Completed"))
GROUP BY Master_DataBase.Function, Master_DataBase.Project_Name, Year([Project_Start_Date])
HAVING (((Master_DataBase.Function)=[Forms]![Navigator_Form]![FilterbyFunction])) OR ((([Forms]![Navigator_Form]![FilterbyFunction]) Is Null)) AND
(((Master_DataBase.Function)=[Forms]![Navigator_Form]![FilterbyYear])) OR ((([Forms]![Navigator_Form]![FilterbyYear]) Is Null))
ORDER BY Count(Master_DataBase.Status_of_Project) DESC;

I have attached the form combo box image for your reference.

enter image description here

Attached one more (Design View) for reference:

enter image description here

Solution

  • Start by fixing your error:

    HAVING (((Master_DataBase.Function)=[Forms]![Navigator_Form]![FilterbyFunction])) OR ((([Forms]![Navigator_Form]![FilterbyFunction]) Is Null)) AND (((Master_DataBase.Function)=[Forms]![Navigator_Form]![FilterbyYear])) OR ((([Forms]![Navigator_Form]![FilterbyYear]) Is Null))

    to

    HAVING (((Master_DataBase.Function)=[Forms]![Navigator_Form]![FilterbyFunction])) OR ((([Forms]![Navigator_Form]![FilterbyFunction]) Is Null)) AND (((Year(Master_DataBase.Project_Start_Date))=[Forms]![Navigator_Form]![FilterbyYear])) OR ((([Forms]![Navigator_Form]![FilterbyYear]) Is Null))

    Edit:

    I would just simplify the whole expression to:

    HAVING Master_DataBase.Function=Nz([Forms]![Navigator_Form]![FilterbyFunction],Master_DataBase.Function)
AND Year(Master_DataBase.Project_Start_Date)=Nz([Forms]![Navigator_Form]![FilterbyYear],Year(Master_DataBase.Project_Start_Date))