Search code examples
sqlvbams-accesssearch-form

WHERE, AND, OR Combination

I have 2 objects, 1st is a list box of the customers and the 2nd is a textfield used as a keyword search

If the user selects the customer name from the listbox the correct corresponding records are selected for the customer in the subform. But when I have a customer name selected AND use a keyword, the keyword searches through ALL records of ALL customer names. I believe my AND statement in my Where clause is incorrect. If the user has a customer name selected and uses a keyword then I want that keyword only searched for the selected customer. Below are the 2 functions I'm using.

Private Sub CustomerListbox_Click()

Dim SQL As String

SQL = "SELECT tblPartsAndConsumables.DESCRIPTION, tblPartsAndConsumables.[P/N], tblPartsAndConsumables.[S/N], tblPartsAndConsumables.[B/N], tblPartsAndConsumables.QTY, tblPartsAndConsumables.[EXPIRY DATE], tblPartsAndConsumables.LOCATION, tblPartsAndConsumables.Attachments " _
& " FROM tblPartsAndConsumables " _
& "WHERE [Customer Name] LIKE  '*" & Me.CustomerListbox.Value & "*' " _
& " ORDER BY tblPartsAndConsumables.DESCRIPTION, tblPartsAndConsumables.[P/N]; "

Me.SubFormSearch.Form.RecordSource = SQL
Me.SubFormSearch.Form.Requery

End Sub

Private Sub Searchbox_Change()

Dim SQL As String

SQL = "SELECT tblPartsAndConsumables.[DESCRIPTION], tblPartsAndConsumables.[P/N], tblPartsAndConsumables.[S/N], tblPartsAndConsumables.[B/N], tblPartsAndConsumables.QTY, tblPartsAndConsumables.[EXPIRY DATE], tblPartsAndConsumables.LOCATION, tblPartsAndConsumables.Attachments " _
& " FROM tblPartsAndConsumables " _
& "WHERE [Customer Name] LIKE  '*" & Me.CustomerListbox.Value & "*' " _
& "AND [DESCRIPTION] LIKE  '*" & Me.Searchbox.Text & "*' " _
& "OR [P/N] LIKE  '*" & Me.Searchbox.Text & "*' " _
& "OR [S/N] LIKE  '*" & Me.Searchbox.Text & "*' " _
& "OR [B/N] LIKE  '*" & Me.Searchbox.Text & "*'" _
& " ORDER BY tblPartsAndConsumables.[DESCRIPTION], tblPartsAndConsumables.[P/N]; "

Me.SubFormSearch.Form.RecordSource = SQL
Me.SubFormSearch.Form.Requery

End Sub
Solution

  • You should wrap the condition joined by OR condition with () 

        SQL = "SELECT tblPartsAndConsumables.[DESCRIPTION], tblPartsAndConsumables.[P/N], tblPartsAndConsumables.[S/N], tblPartsAndConsumables.[B/N], tblPartsAndConsumables.QTY, tblPartsAndConsumables.[EXPIRY DATE], tblPartsAndConsumables.LOCATION, tblPartsAndConsumables.Attachments " _
    & " FROM tblPartsAndConsumables " _
    & "WHERE [Customer Name] LIKE  '*" & Me.CustomerListbox.Value & "*' " _
    & "AND ( [DESCRIPTION] LIKE  '*" & Me.Searchbox.Text & "*' " _
    & "OR [P/N] LIKE  '*" & Me.Searchbox.Text & "*' " _
    & "OR [S/N] LIKE  '*" & Me.Searchbox.Text & "*' " _
    & "OR [B/N] LIKE  '*" & Me.Searchbox.Text & "*' ) " _
    & " ORDER BY tblPartsAndConsumables.[DESCRIPTION], tblPartsAndConsumables.[P/N]; "