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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Pawan KumarDatabase ExpertCommented:
What is the criteria to include AND / OR...
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Dale FyeOwner, Developing Solutions LLCCommented:
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
PatHartmanCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.