Access窗体查询逻辑异常：选中客户后关键词搜索范围错误

你猜的完全正确！问题出在SQL语句的逻辑运算符优先级上——在SQL里，AND的优先级比OR高，所以你当前的WHERE子句会被数据库解析成：

([Customer Name] LIKE '*客户名*' AND [DESCRIPTION] LIKE '*关键词*') 
OR [P/N] LIKE '*关键词*' 
OR [S/N] LIKE '*关键词*' 
OR [B/N] LIKE '*关键词*'

这就导致只要关键词匹配了P/N、S/N或B/N中的任意一个，不管客户名称是否匹配，都会被筛选出来，完全绕过了客户的限定范围。

修复方法：给OR条件组加括号 #

把所有关键词相关的OR条件用括号包裹，让它们成为一个逻辑整体，再和客户名称的条件用AND关联，这样逻辑就变成了**「客户匹配」AND「任意关键词字段匹配」**，正好符合你想要的「先锁定客户，再在该客户范围内搜索关键词」的需求。

修改后的Searchbox_Change函数代码如下：

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

额外小提示（可选） #

如果存在用户未选中客户就使用搜索框的场景，你可以加个判断逻辑，避免CustomerListbox.Value为空导致的筛选异常，比如：

' 在构建WHERE子句前先判断
Dim customerFilter As String
If Not IsNull(Me.CustomerListbox.Value) And Me.CustomerListbox.Value <> "" Then
    customerFilter = "[Customer Name] LIKE '*" & Me.CustomerListbox.Value & "*' AND "
Else
    customerFilter = ""
End If
' 然后把customerFilter拼到SQL里
SQL = "SELECT ... FROM ... WHERE " & customerFilter & "( [DESCRIPTION] LIKE ... ) ORDER BY ..."

内容的提问来源于stack exchange，提问作者4GetFullOf