Dear All,
I have search form with many fields on it. The majority of fields should work with AND operator, But I also have several fields on form where I enter several criteria for the same field and that several criterias should work with OR operator.
I am new to VBA for Access and only can understand and read the code, so I find the code for AND operator, tried to combine the code with OR, but my attempt failed. So I kindly ask you to help me with it.
Please see example of code below. and please advise how can I insert there a code for Qual1, Qual2 and Quial3 with OR.
Code:
Private Sub Search_Click()
On Error GoTo Err_Search_Click
Dim strWhere As String
Dim lngLen As Long
Const ConJetDate = "\#mm\/dd\/yyyy\#"
'--------------------------------DATES SECTION------------------------------------------
'BirthDate Field
If Not IsNull(Me.BirthDate1) Then
strWhere = strWhere & "([BirthDate] >= " & Format(Me.BirthDate1, ConJetDate) & ") AND "
End If
If Not IsNull(Me.BirthDate2) Then
strWhere = strWhere & "([BirthDate] <= " & Format(Me.BirthDate2, ConJetDate) & ") AND "
End If
'TCAcceptDate Field
If Not IsNull(Me.TCAcceptDate1) Then
strWhere = strWhere & "([TCAcceptDate] >= " & Format(Me.TCAcceptDate1, ConJetDate) & ") AND "
End If
If Not IsNull(Me.TCAcceptDate2) Then
strWhere = strWhere & "([TCAcceptDate] <= " & Format(Me.TCAcceptDate2, ConJetDate) & ") AND "
End If
'ContSentDate Field
If Not IsNull(Me.ContSentDate1) Then
strWhere = strWhere & "([ContSentDate] >= " & Format(Me.ContSentDate1, ConJetDate) & ") AND "
End If
If Not IsNull(Me.ContSentDate2) Then
strWhere = strWhere & "([ContSentDate] <= " & Format(Me.ContSentDate2, ConJetDate) & ") AND "
End If
'MANY OTHER FIELDS WITH THE SAME AND LOGIC
lngLen = Len(strWhere) - 5
If lngLen <= 1 Then
MsgBox "Enter Criteria!.", vbInformation, "No criteria indicated!"
Else
strWhere = Left$(strWhere, lngLen)
End If
If IsNull(Me.Qual1) = False Or IsNull(Me.Qual2) = False Then strWhere = strWhere & ")"
DoCmd.OpenForm "frmAllData", acFormDS, , strWhere
Exit_Search_Click:
Exit Sub
Err_Search_Click:
MsgBox Err.Description
Resume Exit_Search_Click
End Sub
Thank you in advance!!!