我正在制作一個存盤大型機電設備所有組件的資料庫。資料庫存盤所有組件的參考資訊（組件型別、系統、子系統等）以及組件詳細資訊（寬度、材料等）。我正在處理一個帶有組合框的表單，用戶填寫該組合框以生成一個報告，其中僅包含與過濾條件匹配的特定組件表單，并選擇了組件型別和子系統。我可以很好地生成報告，但它不會一直正確過濾。我相信這與我正在應用的多過濾器字串有關。例如，如果我選擇了 Component Type = Ball Bearing or Shaft 和 Subsystem = Mooring 1 或 Floats 2。我作為過濾器應用的字串是：
[ComponentType] = 'Ball Bearing' 或 [ComponentType] = 'Shaft' And [Subsystem] = 'Mooring 1' 或 [Subsystem] = 'Floats 2'
但是當我收到報告時，它有條目，它不應該有Filtered report。此報告應僅包含 Mooring 1 或 Floats 2 子系統中的軸或滾珠軸承。此示例只有 1 個不應該存在的專案，但其他過濾器也會發生這種情況。我在 B 子系統中僅選擇 X 和 Y 組件型別，但它回傳 B 子系統中的 X、Y 和 Z 組件型別。
這是我的代碼。
Private Sub Report_Open(Cancel As Integer)
Dim frm As Form 'sets frm to a Form type
Dim strFilter, comptypestrFilter, systemstrFilter, subsystemstrFilter, assemblystrFilter, subassemblystrFilter As String 'set strFilter as string type
Set frm = Forms!FilterComponentListFrm 'form must be open. makes frm into abbreviation
strFilter = "" 'Create empty filter string
comptypestrFilter = "" 'Create empty filter string for comptype
systemstrFilter = "" 'Create empty filter string for system
subsystemstrFilter = "" 'Create empty filter string for subsystem
assemblystrFilter = "" 'Create empty filter string for assembly
subassemblystrFilter = "" 'Create empty filter string for subassembly
'Check if length of all combo boxes plus a blank string=0. If True, Don't apply filter and exit sub.
If Len("" & frm![ComponentTypeFilterCombo] & frm![SystemFilterCombo] & frm![SubsystemFilterCombo] & frm![AssemblyFilterCombo] & frm![SubassemblyFilterCombo] & _
frm![ComponentTypeFilterCombo2] & frm![SystemFilterCombo2] & frm![SubsystemFilterCombo2] & frm![AssemblyFilterCombo2] & frm![SubassemblyFilterCombo2]) = 0 Then
Me.Filter = ""
Me.FilterOn = False
Exit Sub
'If length of sum of all combo boxes <>0
Else
'The first 5 blocks of If/ElseIf/ElseIf/EndIf check to see if the combo boxes are selected or empty and puts the expression into a string.
'If CompTypeFilterCombo 1 and 2 is selected then set comptype filter string as CompTypeFilter 1 and 2
If Len("" & frm![ComponentTypeFilterCombo]) > 0 And Len("" & frm![ComponentTypeFilterCombo2]) > 0 Then
comptypestrFilter = "[ComponentType] = '" & frm![ComponentTypeFilterCombo] & "'" & " Or " & "[ComponentType] = '" & frm![ComponentTypeFilterCombo2] & "'"
'If only CompTypeFilterCombo 1 is selected then set comptype filter string as CompTypeFilter 1
ElseIf Len("" & frm![ComponentTypeFilterCombo]) > 0 Then
comptypestrFilter = "[ComponentType] = '" & frm![ComponentTypeFilterCombo] & "'"
'If only CompTypeFilterCombo 2 is selected then set comptype filter string as CompTypeFilter 2
ElseIf Len("" & frm![ComponentTypeFilterCombo2]) > 0 Then
comptypestrFilter = "[ComponentType] = '" & frm![ComponentTypeFilterCombo2] & "'"
End If
'If SystemFilterCombo 1 and 2 is selected then set system filter string as systemfiter 1 and 2
If Len("" & frm![SystemFilterCombo]) > 0 And Len("" & frm![SystemFilterCombo2]) > 0 Then
systemstrFilter = "[System] = '" & frm![SystemFilterCombo] & "'" & " Or " & "[System] = '" & frm![SystemFilterCombo2] & "'"
'If SystemFilterCombo 1 is selected only, then add SystemFilter 1 into system filter string
ElseIf Len("" & frm![SystemFilterCombo]) > 0 Then
systemstrFilter = "[System] = '" & frm![SystemFilterCombo] & "'"
'If SystemFilterCombo 2 is selected only, then add SystemFilter 2 into system filter string
ElseIf Len("" & frm![SystemFilterCombo2]) > 0 Then
systemstrFilter = "[System] = '" & frm![SystemFilterCombo2] & "'"
End If
'If SubsystemFilterCombo 1 and 2 is selected then set subsystem filter string as subsystemfiter 1 and 2
If Len("" & frm![SubsystemFilterCombo]) > 0 And Len("" & frm![SubsystemFilterCombo2]) > 0 Then
subsystemstrFilter = "[Subsystem] = '" & frm![SubsystemFilterCombo] & "'" & " Or " & "[Subsystem] = '" & frm![SubsystemFilterCombo2] & "'"
'If SubsystemFilterCombo 1 is selected only, then add SubsystemFilter 1 into subsystem filter string
ElseIf Len("" & frm![SubsystemFilterCombo]) > 0 Then
subsystemstrFilter = "[Subsystem] = '" & frm![SubsystemFilterCombo] & "'"
'If SubsystemFilterCombo 2 is selected only, then add SubsystemFilter 2 into subsystem filter string
ElseIf Len("" & frm![SubsystemFilterCombo2]) > 0 Then
subsystemstrFilter = "[Subsystem] = '" & frm![SubsystemFilterCombo2] & "'"
End If
'If AssemblyFilterCombo 1 and 2 is selected then set Assembly filter string as Assembly 1 and 2
If Len("" & frm![AssemblyFilterCombo]) > 0 And Len("" & frm![AssemblyFilterCombo2]) > 0 Then
assemblystrFilter = "[Assembly] = '" & frm![AssemblyFilterCombo] & "'" & " Or " & "[Assembly] = '" & frm![AssemblyFilterCombo2] & "'"
'If AssemblyFilterCombo 1 is selected only, then add AssemblyFilter 1 into assembly filter string
ElseIf Len("" & frm![AssemblyFilterCombo]) > 0 Then
assemblystrFilter = "[Assembly] = '" & frm![AssemblyFilterCombo] & "'"
'If AssemblyFilterCombo 2 is selected only, then add AssemblyFilter 2 into assembly filter string
ElseIf Len("" & frm![AssemblyFilterCombo2]) > 0 Then
assemblystrFilter = "[Assembly] = '" & frm![AssemblyFilterCombo2] & "'"
End If
'If SubassemblyFilterCombo 1 and 2 is selected then set Subassembly filter string as Subassembly 1 and 2
If Len("" & frm![SubassemblyFilterCombo]) > 0 And Len("" & frm![SubassemblyFilterCombo2]) > 0 Then
subassemblystrFilter = "[Subassembly] = '" & frm![SubassemblyFilterCombo] & "'" & " Or " & "[Subassembly] = '" & frm![SubassemblyFilterCombo2] & "'"
'If SubassemblyFilterCombo 1 is selected only, then add SubassemblyFilter 1 into subassembly filter string
ElseIf Len("" & frm![SubassemblyFilterCombo]) > 0 Then
subassemblystrFilter = "[Subassembly] = '" & frm![SubassemblyFilterCombo] & "'"
'If SubassemblyFilterCombo 2 is selected only, then add SubassemblyFilter 2 into subassembly filter string
ElseIf Len("" & frm![SubassemblyFilterCombo2]) > 0 Then
subassemblystrFilter = "[Subassembly] = '" & frm![SubassemblyFilterCombo2] & "'"
End If
'Checks if each of the individual filters and the overal strFilter is empty. If individual filter and strFilter is empty, sets individual filter to strFilter. If strFilter is not empty, concatenate individual filter onto current strFilter
'Compiles strFilter based on which comboboxes are selected
If Len("" & comptypestrFilter) > 0 Then 'set strFilter to comptypefilter if any comptypefiltercombo box is selected
strFilter = comptypestrFilter
End If
If Len("" & systemstrFilter) > 0 And Len("" & strFilter) > 0 Then 'if systemfilter and strFilter are > 0 then concatenate
strFilter = strFilter & " And " & systemstrFilter
ElseIf Len("" & systemstrFilter) > 0 Then 'if strFilter is not >0 then set strFilter to system Filter
strFilter = systemstrFilter
End If
If Len("" & subsystemstrFilter) > 0 And Len("" & strFilter) > 0 Then 'if subsystemfilter and strFilter are > 0 then concatenate
strFilter = strFilter & " And " & subsystemstrFilter
ElseIf Len("" & subsystemstrFilter) > 0 Then 'if strFilter is not >0 then set strFilter to subsystem Filter
strFilter = subsystemstrFilter
End If
If Len("" & assemblystrFilter) > 0 And Len("" & strFilter) > 0 Then 'if assemblyfilter and strFilter are > 0 then concatenate
strFilter = strFilter & " And " & assemblystrFilter
ElseIf Len("" & assemblystrFilter) > 0 Then 'if strFilter is not >0 then set strFilter to assembly Filter
strFilter = assemblystrFilter
End If
If Len("" & subassemblystrFilter) > 0 And Len("" & strFilter) > 0 Then 'if subassemblyfilter and strFilter are > 0 then concatenate
strFilter = strFilter & " And " & subassemblystrFilter
ElseIf Len("" & subassemblystrFilter) > 0 Then 'if strFilter is not >0 then set strFilter to subassembly Filter
strFilter = subassemblystrFilter
End If
MsgBox strFilter, 0, "strFilter"
Me.Filter = strFilter
Me.FilterOn = True
End If
End Sub
uj5u.com熱心網友回復：
您需要在運算式周圍加上括號。
([ComponentType] = 'Ball Bearing' Or [ComponentType] = 'Shaft') And ([Subsystem] = 'Mooring 1' Or [Subsystem] = 'Floats 2')
這是一個運算子優先級問題。AND之前處理過OR，因此您之前的代碼被讀取為
[ComponentType] = 'Ball Bearing' Or ([ComponentType] = 'Shaft' And ([Subsystem] = 'Mooring 1') Or [Subsystem] = 'Floats 2'.
添加括號控制它的處理方式。首先完成這兩個OR運算式，然后將AND應用于結果，為您提供所需的行為。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/519617.html
標籤：sqlvba筛选