werafa
05-02-2019, 02:05 PM
Hi all,
I have a table of staff names with, where applicable, employment end dates.
I wish to filter the table to show all staff relevant to the current financial year
This requires that I select dates in the current fin year as well as fields with no date
my current code results in no records being selected. can anyone see my error?
thanks
Werafa
Private Sub tglHideYears_Click()
Dim mysheet As Worksheet
Dim myTable As ListObject
Dim filterDate As String
Set mysheet = ThisWorkbook.Worksheets("Staff List")
Set myTable = mysheet.ListObjects("tblStaff")
filterDate = Now()
If Month(filterDate) > 6 Then
filterDate = "30/6/" & Year(filterDate)
Else
filterDate = "30/6/" & Year(filterDate) - 1
End If
If tglHideYears = True Then
'the problem code
myTable.Range.AutoFilter Field:=4, Criteria1:="> 30/6/2018", Operator:=xlAnd, Criteria2:=""""""
tglHideYears.Caption = "Show All Years"
End If
If tglHideYears = False Then
myTable.Range.AutoFilter Field:=4
tglHideYears.Caption = "Show Current Year"
End If
End Sub
update:
myTable.Range.AutoFilter Field:=4, Criteria1:="> 30/6/2018", Operator:=xlOr, Criteria2:="="
causes the 'no employment end dates' to show correctly, but I still do not have the row with EED = 28/11/2018
Thanks
I have a table of staff names with, where applicable, employment end dates.
I wish to filter the table to show all staff relevant to the current financial year
This requires that I select dates in the current fin year as well as fields with no date
my current code results in no records being selected. can anyone see my error?
thanks
Werafa
Private Sub tglHideYears_Click()
Dim mysheet As Worksheet
Dim myTable As ListObject
Dim filterDate As String
Set mysheet = ThisWorkbook.Worksheets("Staff List")
Set myTable = mysheet.ListObjects("tblStaff")
filterDate = Now()
If Month(filterDate) > 6 Then
filterDate = "30/6/" & Year(filterDate)
Else
filterDate = "30/6/" & Year(filterDate) - 1
End If
If tglHideYears = True Then
'the problem code
myTable.Range.AutoFilter Field:=4, Criteria1:="> 30/6/2018", Operator:=xlAnd, Criteria2:=""""""
tglHideYears.Caption = "Show All Years"
End If
If tglHideYears = False Then
myTable.Range.AutoFilter Field:=4
tglHideYears.Caption = "Show Current Year"
End If
End Sub
update:
myTable.Range.AutoFilter Field:=4, Criteria1:="> 30/6/2018", Operator:=xlOr, Criteria2:="="
causes the 'no employment end dates' to show correctly, but I still do not have the row with EED = 28/11/2018
Thanks