By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,763 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Searching Multiple Fields using Single Criteria

P: 1
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.
Expand|Select|Wrap|Line Numbers
  1.     If Not IsNull(Me.cboState) Then
  2.         strWhere = strWhere & "([state] = """ & Me.cboState & """) AND "
  3.     End If
  4.  
  5.     If Not IsNull(Me.cboSurvey1) Then
  6.         strWhere = strWhere & "([surveyor_1] = """ & Me.cboSurvey1 & """) or "
  7.     End If
  8.  
  9.     If Not IsNull(Me.cboSurvey1) Then
  10.             strWhere = strWhere & "([surveyor_2] = """ & Me.cboSurvey1 & """) AND "
  11.     End If
  12.  
  13.  
  14.     If Not IsNull(Me.txtstartdate) Then
  15.         strWhere = strWhere & "([Survey_Start] >= " & Format(Me.txtstartdate, conJetDate) & ") AND "
  16.     End If
  17.     If Not IsNull(Me.txtenddate) Then
  18.         strWhere = strWhere & "([Survey_Start] >= " & Format(Me.txtenddate, conJetDate) & ") AND "
  19.     End If
Attached Files
File Type: txt String Code.txt (726 Bytes, 255 views)
Mar 11 '10 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,607
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.
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Me.cboSurvey1) Then
  2.   strWhere = strWhere & "([surveyor_1] = """ & Me.cboSurvey1 & """) or "
  3. End If
  4.  
  5. If Not IsNull(Me.cboSurvey1) Then
  6.   strWhere = strWhere & "([surveyor_2] = """ & Me.cboSurvey1 & """) AND "
  7. End If
Mar 11 '10 #2

NeoPa
Expert Mod 15k+
P: 31,273
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.
Mar 11 '10 #3

100+
P: 122
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:
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Me.cboSurvey1) Then
  2.   strWhere = strWhere & "([surveyor_1] = """ & Me.cboSurvey1 & """) or "
  3. End If
  4.  
  5. If Not IsNull(Me.cboSurvey1) Then
  6.   strWhere = strWhere & "([surveyor_2] = """ & Me.cboSurvey1 & """) AND "
  7. End If
With this:
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Me.cboSurvey1) Then
  2.   strWhere = strWhere & "(([surveyor_1] = """ & Me.cboSurvey1 & """) OR "
  3.   strWhere = strWhere & "([surveyor_2] = """ & Me.cboSurvey1 & """)) AND "
  4. 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.
Mar 12 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.