如何修改VBA脚本使查询结果包含特定字段为Null的记录

Access的Jet/ACE SQL引擎中，LIKE '*'通配符规则仅能匹配非空值，字段为NULL的记录不会被该规则命中，你代码中所有“不过滤该字段”的逻辑都写的是[字段] like '*'，因此会自动排除对应字段为NULL的记录。

• 替换所有默认不过滤的条件表达式：将[字段名] LIKE '*'统一修改为([字段名] LIKE '*' OR [字段名] IS NULL)，覆盖以下变量赋值逻辑：
  1. chk_AuditEX未勾选时的Extern变量
  2. chk_AuditIN未勾选时的Intern变量
  3. cbo_CustomerLocations为空时的CustomerLocation、CustomerLocationPlace变量
  4. cbo_Customers为空时的Customer变量
  5. txt_ExecutionDateTo为空时的ExecutionDate变量
  6. cbo_Material为空时的Material变量
• 修复条件拼接语法错误：原代码拼接strCriteria时存在多余的&符号、AND前后无空格、ExecutionDate和Material之间缺失AND的问题，会导致SQL语法报错，需要同步修正。

Function SearchCriteria()

Dim Customer As String, CustomerLocation As String, CustomerLocationPlace As String, ExecutionDate As String, Material As String
Dim Intern As String, Extern As String
Dim task As String, strCriteria As String

If Me.chk_AuditEX = True Then
    Extern = "[AuditEX] = " & Me.chk_AuditEX
Else
    Extern = "([AuditEX] like '*' OR [AuditEX] Is Null)"
End If

 If Me.chk_AuditIN = True Then
    Intern = "[AuditIN] = " & Me.chk_AuditIN
Else
    Intern = "([AuditIN] like '*' OR [AuditIN] Is Null)"
End If

If IsNull(Me.cbo_CustomerLocations) Then
    CustomerLocation = "([CustomerLocationID] like '*' OR [CustomerLocationID] Is Null)"
    CustomerLocationPlace = "([LocationCompanyPlace] like '*' OR [LocationCompanyPlace] Is Null)"
Else
    CustomerLocation = "[LocationCompanyName] = '" & Me.cbo_CustomerLocations.Column(0) & "'"
    CustomerLocationPlace = "[LocationCompanyPlace] = '" & Me.cbo_CustomerLocations.Column(1) & "'"
End If


If IsNull(Me.cbo_Customers) Then
    Customer = "([CustomerID] like '*' OR [CustomerID] Is Null)"
Else
    Customer = "[CustomerID] = " & Me.cbo_Customers
End If

If IsNull(Me.txt_ExecutionDateTo) Then
    ExecutionDate = "([ExecutionDate] like '*' OR [ExecutionDate] Is Null)"
Else
    If IsNull(Me.txt_ExecutionDateFrom) Then
        ExecutionDate = "[ExecutionDate] like '" & Me.txt_ExecutionDateTo & "'"
    Else
        ExecutionDate = "([ExecutionDate] >= #" & Format(Me.txt_ExecutionDateFrom, "mm/dd/yyyy") & "# And [ExecutionDate] <= #" & Format(Me.txt_ExecutionDateTo, "mm/dd/yyyy") & "#)"
    End If
End If

If IsNull(Me.cbo_Material) Or Me.cbo_Material = "" Then
    Material = "([MaterialID] like '*' OR [MaterialID] Is Null)"
ElseIf Me.cbo_Material = 6 Then
    Material = "[MaterialID] in (" & TempVars!tempMaterial & ")"
Else
    Material = "([MaterialID] = " & Me.cbo_Material & ")"
End If

strCriteria = Customer & " AND " & CustomerLocation & " AND " & CustomerLocationPlace & " AND " & _
ExecutionDate & " AND " & Material & " AND " & Extern & " AND " & Intern
            
task = "Select * from qry_Administration where (" & strCriteria & ") order by ExecutionDate DESC"

Debug.Print (task)

Me.Form.RecordSource = task
Me.Form.Requery
End Function

内容的提问来源于stack exchange，提问作者Mark Bakker