Multiple criteria in query 2

[misuser2k7]

I have a table which I am trying to query with multiple criteria. The table which has other columns has 3 that are of interest. These 3 criteria on the 3 columns come from 3 combo boxes on a form. The 3 columns of interest are SurveyName, SurveySubject And SurveyResult and they are in a table called ‘Surveys’. The other columns contain other details of surveys. I created an Access query in which I referenced the combo boxes are the sources of the criteria. What the query is supposed to do is find the rows where all 3 criteria are exclusively true. For example, When a survey name is selected from the survey name combo box along with a survey subject and/or survey result from their respective combo boxes, only the rows where are 3 criteria are being answered should be displayed in the output but this not happening. What is happening is that all rows where what was entered as the 3 criteria are mentioned are displayed. What this means is that if I only make a selection in 2 combo boxes leaving the third empty, the result displayed contains all the results of the 2nd criteria with all results for the 1st criteria. SurveyName has particulars like CustSpend, CustSatisf, CustRetBiz and so on. SurveySubject has CustPurchEncounter, CustOrdDelivery, CustReturns and so on. SurveyResult has positive, negative, neutral. So if the user wanted to know about customer survey responses for Customer Spending survey about purchase encounter, the user should see only survey results for CustSpend and CustPurchEncounter regardless of SurveyResult. Instead what is happening is that all rows with CustSpend are displayed with all rows with CustPurchEncounter i.e. the other two criteria are ignored.

I tried entering the 3 criteria with both the ‘And’ and ‘Or’ options in different combinations of the ‘criteria’ and ‘or’ rows in the Access query design view. Putting the criteria on the same row (‘and’ option) resulted in no rows being displayed and putting them on different rows (‘or’ option) displays the result in the manner mentioned above.

Is there a way of writing a query that will result in only displaying rows when the 3 criteria are met strictly even if one or two combo boxes are left empty by the user i.e. the user may just us/enter two criteria instead of all 3?

Thank you all for your help.

 

[SkipVought]

Hi,

Is there a way of writing a query that will result in only displaying rows when the 3 criteria are met strictly even if one or two combo boxes are left empty by the user i.e. the user may just us/enter two criteria instead of all 3?

Not without VBA code.

Please post your SQL code.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MajP]

You can do this in the query by referencing form controls but I find that cumbersome and very inflexible. I like to build a function that will return me the where string. I then can use that as a filter for a form or use it to build a sql string for a recordset or a querydef.
Untested

Public Funtion GetSurveyFilter() as string
  dim strWhere as string
  'One for each control
  dim strName as string
  dim strSubject as string
  dim strResult as string

  'this is going to be an AND query

  if not trim(cboSurveyName & " ") = "" then
    StrName = "SurveyName = '" & me.cboSurveyName & "' AND "
  end if
  
  if not trim(cboSubject & " ") = "" then
    StrSubject = "SurveySubject = '" & me.cboSubject & "' AND "
  end if
  
  if not trim(cboResult & " ") = "" then
    StrResult = "SurveyResult = '" & me.cboResult & "' AND "
  end if

  strWhere = StrName & strSubject & StrResult
  
  if not strWhere = "" then
    'the string will always end with an AND
     ' so remove it 
    strWhere = left(strWhere, len(strWhere) - 4)
  end if 
  'before using check it a few times by seeing if it makes the correct filter
  debug.print strWhere
  GetSurveyFilter = strWhere
end function

So if I wanted to filter the form on the after update of each combo

Public Sub CboSurveySubject_AfterUpdate()
  me.filter = GetSurveyFilter
  me.filterOn = (me.filter <> "")
end sub

 

[MajP]

Not without VBA code

Yes you can. The trick is to also check for isnull in case a combo is not used
here is the example

http://www.fabalou.com/access/queries/comboboxes_criteria.asp

So for each control you have something like

WHERE City=[Forms]![SearchForm]![City] Or IsNull([Forms]![SearchForm]![City])

I personally never could decipher how this resolves, but know it works.

 

[SkipVought]

That was a Major revelation!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[misuser2k7]

MajP your solution worked great! Exactly what I was looking for!

Thank you!