Using AND and OR in custom search box

I would like to make a custom search form that finds records based on the data that is entered into the form. I am now using only AND but would like to be able to use AND and also OR in the SQL statement.
Melvin HeatwoleDeveloperAsked:
Who is Participating?
 
PatHartmanConnect With a Mentor Commented:
Excellent article Dale but it doesn't address the fact that LIKE is a string operation and will not necessarily return the expected results when used on numeric or date fields.  I tried to find the error situations but it's been a long time since I ran into them.  Perhaps it is possible that the issues have been fixed with A2016 or perhaps the anomalies only occurred with SQL Server BE's.

Melvin, as you can see by Dale's article, it's not so easy to combine AND and OR and NOT operators in a single expression.  If all you ever have are two expressions, then swapping AND for OR isn't relevant but as soon as you add a third expression, you need to include parentheses in order to ensure that the expression is evaluated as you expect it to be.

When you say a OR b AND c  Do you mean?

(a OR b) AND c
or
a OR (b and c)

Because the second interpretation is what you get without parentheses.  AND takes precedence over OR so the ANDs are evaluated first and NOTs are evaluated before AND and everything WITHIN parentheses is evaluated before the inner expressions are compared to the larger expression.
0
 
Pawan KumarDatabase ExpertCommented:
can you show me your SQL...and what you need?
0
 
Melvin HeatwoleDeveloperAuthor Commented:
not sure if this is the best way to post my code, but here it is:
Private Sub cmdSearch_Click()
    Dim strSQL As String
   
   
    strSQL = ""
   
    If Not IsNothing(Me.txtFromDate) And IsNothing(Me.txtToDate) Then
        MsgBox "If you enter a date in the From Date, you must also enter a date in the To Date.", vbInformation, "MISSING DATE"
        Me.txtToDate.SetFocus
   
  Else
   
       

    If Not IsNothing(Me.txtFromDate) Then
        strSQL = strSQL & " dtmCalendarDate Between #" & Me.txtFromDate & "# And #" & Me.txtToDate & "# And "
    End If
 End If
If (Me.chkExactSearch) = True Then

    If Not IsNothing(Me.Group) Then
        strSQL = strSQL & " strGroup Like """ & Me.Group & """ And "
    End If
   
    If Not IsNothing(Me.FirstName) Then
        strSQL = strSQL & " strFirstName Like """ & Me.FirstName & """ And "
    End If
   
    If Not IsNothing(Me.LastName) Then
        strSQL = strSQL & " strLastName Like """ & Me.LastName & """ And "
    End If
   
    If Not IsNothing(Me.Group) Then
        strSQL = strSQL & " strGroup Like """ & Me.Group & """ And "
    End If
   
    If Not IsNothing(Me.Type) Then
        strSQL = strSQL & " strType Like """ & Me.Type & """ And "
    End If
   
 
   
   
ElseIf (Me.chkExactSearch) = False Then

    If Not IsNothing(Me.Group) Then
        strSQL = strSQL & " strGroup Like ""*" & Me.Group & "*"" And "
    End If
     
    If Not IsNothing(Me.LastName) Then
        strSQL = strSQL & " strLastName Like ""*" & Me.LastName & "*"" And "
    End If

    If Not IsNothing(Me.FirstName) Then
        strSQL = strSQL & " strFirstName Like ""*" & Me.FirstName & "*"" And "
    End If
   
    If Not IsNothing(Me.Group) Then
        strSQL = strSQL & " strGroup Like ""*" & Me.Group & "*"" And "
    End If
   
    If Not IsNothing(Me.Type) Then
        strSQL = strSQL & " strType Like ""*" & Me.Type & "*"" And "
    End If
   
   
 
   
End If
   
     If strSQL = "" Then
        MsgBox "No criteria specified.", 32
        Exit Sub
    End If
   
'Trim off last 4 characters, which will always be 'AND '.
    If Right(strSQL, 4) = "And " Then
        strSQL = Mid(strSQL, 1, Len(strSQL) - 4)
    End If
    If DCount("*", "qryDates", strSQL) < 1 Then
        MsgBox "The search returned no records.", 32
   Else
       DoCmd.OpenForm "frmDates"
       Forms!frmDates.RecordSource = " Select * From qryDates Where " & strSQL & ";"
       DoCmd.Close acForm, "frmDateSearch", acSaveNo
       Forms!frmDates.SetFocus
    End If
   
   
    Dim strSQLCal As String
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qryDELETEtblCalendarDates", acViewNormal
        strSQLCal = " INSERT INTO tblCalendarDates " _
        & "SELECT * " _
        & "FROM [qryDates]Where " & strSQL & ";"
        DoCmd.RunSQL strSQLCal
        DoCmd.SetWarnings True

End Sub

Notice that the statements end with AND then the AND gets trimmed off at the end. I would like to be able for the user to use either AND or OR and then it would trim off either one.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Pawan KumarDatabase ExpertCommented:
What is the criteria to include AND / OR...
0
 
Dale FyeCommented:
You might want to take a look at my article on complex text filters, located here.

It includes a sample database.
1
 
Melvin HeatwoleDeveloperAuthor Commented:
Between each field in the form I would like to make a combo box that has Or and AND. The user would then select either of them and would fill in the next field on the form, etc.
0
 
Dale FyeConnect With a Mentor Commented:
Thanks, Pat.  That article was written way back in 2011, when I first started contributing to EE.  I was working on a database for work where users explicitly wanted to search text and memo fields, so there was no attempt to address numeric values.

I still use code very similar to that on databases which contain a lot of text or memo fields that users might want to search.

Dale
0
 
Melvin HeatwoleDeveloperAuthor Commented:
Thanks for your input. I'll keep working on it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.