Empty textfield displays all in query

[SBBmaster09]

I have a query for Master Data, then I have a form where I can select in dropdowns what will be the filters in the output file of the Master Data. I need a code in the query table where, if the 1st dropdown is empty and 2nd dropdown has value, the output file will only display the Master Data with the selected data from the 2nd. :banghead:

I am able to do this with start and end dates using the code below:

>=[Forms]![frm_ExtractMainTable]![DateFrom].[value] And <=[Forms]![frm_ExtractMainTable]![DateTo].[value]

Is there any codes I can input where if the dropdown is empty it will output all the data without the filters. And if there is 1 field with value, only that value will be used as filters.


Thank you.

 

[Ranman256]

Test all controls for a possible filter then build the where clause.

 if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value
     'remove 1st And
sWhere= mid(sWhere,4)
   'just use the filter
 me.filter = sWhere
me.filterOn = true
    'OR   
   'apply the sql to the form
 sSql = "SELECT * FROM tblCompany WHERE " & sWhere
 
     'save the sql as a qry or open the sql
set qdf = currentdb.querdefs("qsResults")
qdf.sql = ssql
qdf.close
docmd.openquery qdf.name

 

[SBBmaster09]

It was the one I need to input inside the Query table.. not outside the form. It was like when i select a start and end date it will generate depending on the date values. But now I need to filter the data to Group, Team, etc. I attached the screenshot where I need to input it, the code above works for date now I dont know how it works for an ordinary dropdowns.