Incorrect Syntax - Microsoft Access VBA data filter

I think this should be an easy one but I am struggling to find the correct way to write this and am running out of time to complete.

I have an Access form that uses multiple drop down boxes to filter the records to display in the form. I am attempting to add one more filter. The issue is, my previous filters have all been String format and they work perfectly. The new filter is based on a calculated filed that produces the Year that the record was worked. So I am getting a Data Type Mismatch error. I tried Declaring a new variable with Date format but that gave me an error that says Missing Operator.

My goal is to add cboYearAudited to the list of filters. This would apply only when "Complete" was selected from the cboStatus dropdown box.

Here is my code:

        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

I tried changing the field type to Short text and in this case the I get no errors but also, nothing happens. The selection in the drop down box does not appear to do anything.

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

CodePudding user response：

Hy,

Is there a reason why the field : MyFilterYear is a date ? I suggest you use a string.

So what happens then is :

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

The result should be an SQL String :

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

The # marks are important for filtering dates.