Searching Multiple Fields using Single Criteria

[marsh44]

I am using a multiple filters to search for specific criteria. In one of the filters criteria, I need to use the OR instead of the AND as illustrated below. When insterting the OR it will not recognize the other criteria.

    If Not IsNull(Me.cboState) Then
        strWhere = strWhere & "([state] = """ & Me.cboState & """) AND "
    End If
      
    If Not IsNull(Me.cboSurvey1) Then
        strWhere = strWhere & "([surveyor_1] = """ & Me.cboSurvey1 & """) or "
    End If
    
    If Not IsNull(Me.cboSurvey1) Then
            strWhere = strWhere & "([surveyor_2] = """ & Me.cboSurvey1 & """) AND "
    End If
   
    
    If Not IsNull(Me.txtstartdate) Then
        strWhere = strWhere & "([Survey_Start] >= " & Format(Me.txtstartdate, conJetDate) & ") AND "
    End If
    If Not IsNull(Me.txtenddate) Then
        strWhere = strWhere & "([Survey_Start] >= " & Format(Me.txtenddate, conJetDate) & ") AND "
    End If

[ADezii]

Aren't these two Statements contradictory. If cboSurvey Is Not Null, the first If..End If will be overwritten by the second If...End If. Not only that, the criteria is different in each case based on the same condition.

If Not IsNull(Me.cboSurvey1) Then
  strWhere = strWhere & "([surveyor_1] = """ & Me.cboSurvey1 & """) or "
End If
    
If Not IsNull(Me.cboSurvey1) Then
  strWhere = strWhere & "([surveyor_2] = """ & Me.cboSurvey1 & """) AND "
End If

[NeoPa]

Please reread your question and consider updating it so that it makes sense.

Remember, we don't have your project in front of us so the accuracy of your explanation (question) is important. Hence it's so important to phrase it correctly, so that it makes sense.

[gershwyn]

If I'm understand your problem correctly (and that I'm not sure of) I think the OR condition needs to be isolated with parentheses. As it is set up now, if the state and surveyor match we can ignore the date requirements.

Try replacing this code:

If Not IsNull(Me.cboSurvey1) Then
  strWhere = strWhere & "([surveyor_1] = """ & Me.cboSurvey1 & """) or "
End If
 
If Not IsNull(Me.cboSurvey1) Then
  strWhere = strWhere & "([surveyor_2] = """ & Me.cboSurvey1 & """) AND "
End If

With this:

If Not IsNull(Me.cboSurvey1) Then
  strWhere = strWhere & "(([surveyor_1] = """ & Me.cboSurvey1 & """) OR "
  strWhere = strWhere & "([surveyor_2] = """ & Me.cboSurvey1 & """)) AND "
End If

As ADezii pointed out, you are testing for the same condition so these do not need to be within separate IF statements. Note that I added an extra set of parentheses so the OR is evaluated by itself.

If this doesn't work, please post the full code that generates the query and shows how it used and describe in what way it isn't working. The more specific you can be, the better.