Empty textfield displays all in query

SBBmaster09

Registered User.
Local time
Today, 16:46
Joined
Apr 26, 2013
Messages
92
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:
Code:
>=[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.
 
Test all controls for a possible filter then build the where clause.
Code:
 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
 
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.
 

Attachments

  • query.PNG
    query.PNG
    3.6 KB · Views: 79

Users who are viewing this thread

Back
Top Bottom