Filter 2 fields of different type

[Song Su]

I need help on this filter:

My AgentID (lookup to another table) is number type and Report Date is date
type.
Case 1, I want to remove filter
Case 2, I want to show record WITHOUT agent (my code gives me reverse)
Case 3, I want to show only has agent but report date is blank (my code
first part is ok but second part mismatch
Case 4, I want to show only has agent AND report date is NOT blank (my code
not working)

Private Sub fraFilter_AfterUpdate()
Select Case fraFilter
Case 1
Me.Filter = ""
Me.Requery
Case 2
Me.Filter = "AgentID = " & IsNull(Me.AgentID)
Me.FilterOn = True
Case 3
Me.Filter = "AgentID = " & IsNull(Me.AgentID) And "ReportDate =
0"
Me.FilterOn = True
Case 4
Me.Filter = "AgentID = " & Not IsNull(Me.AgentID) And
"ReportDate = " & Not IsNull(Me.ReportDate)
Me.FilterOn = True
End Select
End Sub

 

[Klatuu]

For Case 1, add
Me.FilterOn = False
and remove the requery. Removing the filter cause a requery

For Case 2
Me.Filter = "[AgentID] Is Null"
Filter string can use either VBA or SQL syntax

For Case 3

Me.Filter = "[AgentID] Is Not Null And [ReportDate] Is Null"

For Case 4

Me.Filter = "[AgentID] Is Not Null And [ReportDate] Is Not Null"

 

[Song Su]

Thank you. All work as intended.

For Case 1, add
Me.FilterOn = False
and remove the requery. Removing the filter cause a requery

For Case 2
Me.Filter = "[AgentID] Is Null"
Filter string can use either VBA or SQL syntax

For Case 3

Me.Filter = "[AgentID] Is Not Null And [ReportDate] Is Null"

For Case 4

Me.Filter = "[AgentID] Is Not Null And [ReportDate] Is Not Null"

I need help on this filter:

My AgentID (lookup to another table) is number type and Report Date is
date type.
Case 1, I want to remove filter
Case 2, I want to show record WITHOUT agent (my code gives me reverse)
Case 3, I want to show only has agent but report date is blank (my code
first part is ok but second part mismatch
Case 4, I want to show only has agent AND report date is NOT blank (my
code not working)

Private Sub fraFilter_AfterUpdate()
Select Case fraFilter
Case 1
Me.Filter = ""
Me.Requery
Case 2
Me.Filter = "AgentID = " & IsNull(Me.AgentID)
Me.FilterOn = True
Case 3
Me.Filter = "AgentID = " & IsNull(Me.AgentID) And "ReportDate
= 0"
Me.FilterOn = True
Case 4
Me.Filter = "AgentID = " & Not IsNull(Me.AgentID) And
"ReportDate = " & Not IsNull(Me.ReportDate)
Me.FilterOn = True
End Select
End Sub