語法不正確-MicrosoftAccessVBA資料篩選器-選擇大小寫

我認為這應該是一件容易的事，但我正在努力尋找正確的方法來撰寫它，并且已經沒有時間完成了。

我有一個 Access 表單，它使用多個下拉框來過濾要在表單中顯示的記錄。我正在嘗試再添加一個過濾器。問題是，我以前的過濾器都是字串格式的，它們作業得很好。新過濾器基于生成記錄作業年份的計算檔案。所以我收到資料型別不匹配錯誤。我嘗試使用日期格式宣告一個新變數，但這給了我一個錯誤，提示缺少運算子。

我的目標是將 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

我嘗試將欄位型別更改為短文本，在這種情況下，我沒有收到任何錯誤，但也沒有任何反應。下拉框中的選擇似乎沒有做任何事情。

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

uj5u.com熱心網友回復：

嗨，

該欄位是否有原因： 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#

# 標記對于過濾日期很重要。

標籤：vba 毫秒访问 选择案例

上一篇：用于跟蹤庫存/活存貨的資料庫

下一篇：使用VBA從XMLHTTP請求中捕獲POST請求回應和重定向URL