我有這個帶有一些基于組合框和搜索欄位的基本搜索功能的拆分表單。SearchCriteria在組合框的 OnChange 事件上或通過單擊我的拆分表單頂部的搜索圖示來呼叫函式。
我目前用來過濾我的記錄源的 VBA 代碼qry_Administration：
Function SearchCriteria()
Dim Customer, CustomerLocation, CustomerLocationPlace, ExecutionDate, Material As String
Dim Intern, Extern As String
Dim task, strCriteria As String
If Me.chk_AuditEX = True Then
Extern = "[AuditEX] = " & Me.chk_AuditEX
Else
Extern = "[AuditEX] like '*'"
End If
If Me.chk_AuditIN = True Then
Intern = "[AuditIN] = " & Me.chk_AuditIN
Else
Intern = "[AuditIN] like '*'"
End If
If IsNull(Me.cbo_CustomerLocations) Then
CustomerLocation = "[CustomerLocationID] like '*'"
CustomerLocationPlace = "[LocationCompanyPlace] like '*'"
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 '*'"
Else
Customer = "[CustomerID] = " & Me.cbo_Customers
End If
If IsNull(Me.txt_ExecutionDateTo) Then
ExecutionDate = "[ExecutionDate] like '*'"
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 '*'"
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 & 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
呼叫后SearchCriteria，例如CustomerLocationID= Null 的所有記錄都不會顯示在我的拆分表單上。我需要在哪里編輯我的 VBA 腳本以顯示所有記錄？即使一個欄位是空的。例如：我沒有CustomerlocationID或我還沒有ExecutionDate。
uj5u.com熱心網友回復：
假設您要問的是“當我對某個欄位的搜索條件為空時，無論該欄位中的內容如何，??我如何讓它回傳所有內容？ ”然后像這樣更改您的行：
If IsNull(Me.cbo_Customers) Then
Customer = "[CustomerID] like '*'"
Else
Customer = "[CustomerID] = " & Me.cbo_Customers
End If
對此：
If IsNull(Me.cbo_Customers) Then
Customer = " 1=1 "
Else
Customer = "[CustomerID] = " & Me.cbo_Customers
End If
這會導致 WHERE 過濾忽略該列中的內容。有更復雜的方法可以做到這一點，但對于您的代碼，這是最簡單也是最快的。
另一方面，如果您要問的是“當我對某個欄位的搜索條件為空時，我如何僅回傳該列中具有 NULL 的那些行？ ”然后將您的行更改為：
If IsNull(Me.cbo_Customers) Then
Customer = "[CustomerID] IS NULL "
Else
Customer = "[CustomerID] = " & Me.cbo_Customers
End If
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/371215.html