语法不正确-Microsoft Access VBA数据筛选器-选择大小写

Question

我认为这应该是一个简单的方法，但我正在努力寻找正确的编写方法，而且没有时间完成。

我有一个Access表单，它使用多个下拉框来筛选要在表单中显示的记录。我正试图再添加一个筛选器。问题是，我以前的过滤器都是字符串格式的，它们工作得很好。新的过滤器基于一个计算的字段，该字段产生记录工作的年份。所以我得到了一个数据类型不匹配的错误。我尝试用Date格式声明一个新变量，但这给了我一个错误，说缺少运算符。

我的目标是将cboYearAudited添加到筛选器列表中。这将仅在"；完整的"；是从cboStatus下拉框中选择的。

这是我的代码：

Option Explicit
Private Sub cboStatus_AfterUpdate()
SetFilters
Me.Requery
End Sub
Private Sub cboQuarter_AfterUpdate()
SetFilters
Me.Requery
End Sub
Private Sub cboManager_AfterUpdate()
cboEmployee.Requery
End Sub
Private Sub cboEmployee_AfterUpdate()
SetFilters
Me.Requery
End Sub
Private Sub cboYearAudited_AfterUpdate()
cboEmployee.Requery
End Sub
Private Sub SetFilters()
Dim MyFilter As String
Dim MyFilterYear As Date
Dim c As Control
Select Case Me.cboStatus
Case "Pending Review"
MyFilter = "Auditor Is Null"
Case "Completed"
MyFilter = "AuditDate Is Not Null"
End Select
If Not IsNull(Me.cboQuarter) Then
MyFilter = MyFilter & IIf(MyFilter = "", "", " AND ") & "[AuditName] = '" & Me.cboQuarter & "'"
End If
If Not IsNull(Me.cboEmployee) Then
MyFilter = MyFilter & IIf(MyFilter = "", "", " AND ") & "[Adjuster] = '" & Me.cboEmployee & "'"
End If
If Not IsNull(Me.cboYearAudited) Then
MyFilter = MyFilter & MyFilterYear & IIf(MyFilter = "", "", " AND ") & "[YearAudited] = '" & Me.cboYearAudited & "'"
End If
'MsgBox (MyFilter)
Me.Filter = False
Me.Filter = MyFilter
Me.FilterOn = True
For Each c In Me.Controls
If c.Tag = "Status" Then
c.Value = Null
End If
Next c
End Sub

我尝试将字段类型更改为Short text，在这种情况下，我没有得到任何错误，但也没有发生任何事情。下拉框中的选择似乎没有任何作用。

Private Sub SetFilters()
Dim MyFilter As String
''Dim MyFilterYear As Date
Dim c As Control
Select Case Me.cboStatus
Case "Pending Review"
MyFilter = "Auditor Is Null"
Case "Completed"
MyFilter = "AuditDate Is Not Null"
End Select
If Not IsNull(Me.cboQuarter) Then
MyFilter = MyFilter & IIf(MyFilter = "", "", " AND ") & "[AuditName] = '" & Me.cboQuarter & "'"
End If
If Not IsNull(Me.cboEmployee) Then
MyFilter = MyFilter & IIf(MyFilter = "", "", " AND ") & "[Adjuster] = '" & Me.cboEmployee & "'"
End If
If Not IsNull(Me.cboYearAudited) Then
MyFilter = MyFilter & IIf(MyFilter = "", "", " AND ") & "[YearAudited] = '" & Me.cboYearAudited & "'"
End If
'MsgBox (MyFilter)
Me.Filter = False
Me.Filter = MyFilter
Me.FilterOn = True
For Each c In Me.Controls
If c.Tag = "Status" Then
c.Value = Null
End If
Next c
End Sub

Answer 1

Hy，

字段MyFilterYear是一个日期有原因吗？我建议你用绳子。

那么接下来发生的是：

Dim MyFilter As String
Dim MyFilterYear As String
Dim MyFilterYearValue As Date
Dim c As Control
If Not IsNull(Me.cboQuarter) Then
MyFilter = MyFilter & IIf(MyFilter = "", "", " AND ") & "[AuditName] = '" & Me.cboQuarter & "'"
End If
If Not IsNull(Me.cboEmployee) Then
MyFilter = MyFilter & IIf(MyFilter = "", "", " AND ") & "[Adjuster] = '" & Me.cboEmployee & "'"
End If
If Not IsNull(Me.cboYearAudited) Then
MyFilter = MyFilter & IIf(MyFilter = "", "", " AND ") & "[YearAudited] = '" & Me.cboYearAudited & "'"
End If
'Define the column where you want your filter to happen and add the relevant date
MyFilterYearValue = Date
MyFilterYear = "[FilterYear] = #" & MyFilterYearValue & "#"
MyFilter = MyFilter & " AND " & MyFilterYear
'MsgBox (MyFilter)
Me.Filter = False
Me.Filter = MyFilter
Me.FilterOn = True
For Each c In Me.Controls
If c.Tag = "Status" Then
c.Value = Null
End If
Next c
Debug.Print MyFilter

结果应该是SQL字符串：

[AuditName] = '1' AND [Adjuster] = 'Mathias' AND [YearAudited] = '2022' AND [FilterYear] = #20/02/2022#

#标记对于筛选日期非常重要。