Help with OR's in SQL statement...

[Glytch]

I'm trying to set up a search form with 4 txtboxes for search criteria, each text value passed needs to be compaired 5 fields of my database. If I use only one field for the search everything works OK, but as soon as I add 'OR' to search multiple fields it will return ANY records with ANYTHING in the searched fields. Here's the code that I'm using right now:

Option Compare Database
Option Explicit
Const strSELECT As String = "SELECT * "
Const strFROM As String = "FROM tblCompany_Information "

Private Sub findit_Click()
    Call DoDispatchSearch
    Me.OrderBy = "carrier_name"
End Sub

Sub DoDispatchSearch()
Dim ok As Boolean, sql As String
ok = False
sql = " WHERE "
If Not IsNull(Me.txtSrchSCity) Then
    If sql <> " WHERE " Then
        sql = sql & " And "
    End If
    ok = True
    sql = sql & "[po_c1] OR [po_c2] OR [po_c3] OR [po_c4] OR [po_c5] LIKE '*" & Me.txtSrchSCity & "*'"
End If
If Not IsNull(Me.txtSrchSState) Then
    If sql <> " WHERE " Then
        sql = sql & " And "
    End If
    ok = True
    sql = sql & "[po_s1] OR [po_s2] OR [po_s3] OR [po_s4] OR [po_s5] '" & Me.txtSrchSState & "'"
End If
If Not IsNull(Me.txtSrchCcity) Then
    If sql <> " WHERE " Then
        sql = sql & " And "
    End If
    ok = True
    sql = sql & "[pd_c1] OR [pd_c2] OR [pd_c3] OR [pd_c4] OR [pd_c5] LIKE '*" & Me.txtSrchCcity & "*'"
End If
If Not IsNull(Me.txtSrchCState) Then
    If sql <> " WHERE " Then
        sql = sql & " And "
    End If
    ok = True
    sql = sql & "[pd_s1] OR [pd_s2] OR [pd_s3] OR [pd_s4] OR [pd_s5] LIKE '" & Me.txtSrchCState & "'"
End If

If ok Then
MsgBox "The SQL String: " & sql & ""
    Me.RecordSource = strSELECT & strFROM & sql
    Me.Requery
    Me.Detail.Visible = True
    Me.FormFooter.Visible = True
    DoCmd.MoveSize , , 8500, 6000
End If
fitexit:
Exit Sub
fiterr:
MsgBox Error$
Resume fitexit
End Sub

Thanks in advance for any help offered .

 

[Rauken]

Think you have to do like this?


sql = sql & "[pd_s1] LIKE '" & Me.txtSrchCState & "' OR [pd_s2] LIKE '" & Me.txtSrchCState & "' OR [pd_s3] LIKE '" & Me.txtSrchCState & "' OR [pd_s4] LIKE '" & Me.txtSrchCState & "' OR [pd_s5] LIKE '" & Me.txtSrchCState & "'"

 

[lupins46]

[po_c1] OR [po_c2] OR [po_c3] OR [po_c4] OR [po_c5] LIKE '*" & Me.txtSrchSCity & "*'"

means....

([po_c1] = true) or ([po_c2]=true) ...etc...
or ([po_c5] LIKE '*" & Me.txtSrchSCity & "*'")

po_c1 WILL be true if it is not false and so on so every record will be selected.

If you wish to test each field using the Like criteria you must specify the full rule for each field.

([po_c1] LIKE '*" & Me.txtSrchSCity & "*'")
or
([po_c2] LIKE '*" & Me.txtSrchSCity & "*'")
or....etc

 

[Glytch]

Thanks for the quick responses! I made the changes suggested, however I'm still getting all results with anything in the searched fields... Here is my updated code:

Sub DoDispatchSearch()
Dim ok As Boolean, sql As String
ok = False
sql = " WHERE "
If Not IsNull(Me.txtSrchSCity) Then
    If sql <> " WHERE " Then
        sql = sql & " And "
    End If
    ok = True
    sql = sql & "[po_c1] LIKE '" & Me.txtSrchSCity & "' OR [po_c2] LIKE '" & Me.txtSrchSCity & "' OR [po_c3] LIKE '" & Me.txtSrchSCity & "' OR [po_c4] LIKE '" & Me.txtSrchSCity & "' OR [po_c5] LIKE '" & Me.txtSrchSCity & "'"
End If
If Not IsNull(Me.txtSrchSState) Then
    If sql <> " WHERE " Then
        sql = sql & " And "
    End If
    ok = True
    sql = sql & "[po_s1] LIKE '" & Me.txtSrchSState & "' OR [po_s2] LIKE '" & Me.txtSrchSState & "' OR [po_s3] LIKE '" & Me.txtSrchSState & "' OR [po_s4] LIKE '" & Me.txtSrchSState & "' OR [po_s5] LIKE '" & Me.txtSrchSState & "'"
End If
If Not IsNull(Me.txtSrchCcity) Then
    If sql <> " WHERE " Then
        sql = sql & " And "
    End If
    ok = True
    sql = sql & "[pd_c1] LIKE '" & Me.txtSrchCcity & "' OR [pd_c2] LIKE '" & Me.txtSrchCcity & "' OR [pd_c3] LIKE '" & Me.txtSrchCcity & "' OR [pd_c4] LIKE '" & Me.txtSrchCcity & "' OR [pd_c5] LIKE '" & Me.txtSrchCcity & "'"
End If
If Not IsNull(Me.txtSrchCState) Then
    If sql <> " WHERE " Then
        sql = sql & " And "
    End If
    ok = True
    sql = sql & "[pd_s1] LIKE '" & Me.txtSrchCState & "' OR [pd_s2] LIKE '" & Me.txtSrchCState & "' OR [pd_s3] LIKE '" & Me.txtSrchCState & "' OR [pd_s4] LIKE '" & Me.txtSrchCState & "' OR [pd_s5] LIKE '" & Me.txtSrchCState & "'"
End If

If ok Then
MsgBox "The SQL String: " & sql & ""
    Me.RecordSource = strSELECT & strFROM & sql
    Me.Requery
    Me.Detail.Visible = True
    Me.FormFooter.Visible = True
    DoCmd.MoveSize , , 8500, 6000
End If
fitexit:
Exit Sub
fiterr:
MsgBox Error$
Resume fitexit
End Sub

Hopefully I missed something obvious, I can't see it though .

 

[glalsop]

You will also need to wrap each OR "section" in parentheses:

If Not IsNull(Me.txtSrchSCity) Then
    If sql <> " WHERE " Then
        sql = sql & " And "
    End If
    ok = True
    sql = sql & "[blue]([/blue][po_c1] LIKE '" & Me.txtSrchSCity & "' OR [po_c2] LIKE '" & Me.txtSrchSCity & "' OR [po_c3] LIKE '" & Me.txtSrchSCity & "' OR [po_c4] LIKE '" & Me.txtSrchSCity & "' OR [po_c5] LIKE '" & Me.txtSrchSCity & "'[blue])[/blue]"
End If

so that they will be evaluated as a set.


-Gary

 

[Glytch]

That did it! Thanks again to the people who responded, just saved me hours of pondering .