Date Comparisson Code

[Craw]

Greetings,
I am trying to write code for a command button that will compare two dates. I have a spreadsheet with a list of documents and their approval dates. The approval dates are listed in a column and I have an autofilter setup in said column. What I would like to do is push a button and have have the autofilter compare the current date to that of the documents approval date. Only the documents that are more then 1 year old should show up in the autofilter.

Any ideas?

 

[Craw]

So far I have had help to develop a code that searches a user specified date range. I would like to modify this code so that when the button is clicked it will automatically check the current date and look for records that go back 1 year or more. Is this possible?

Private Sub CommandButton13_Click()
 
Dim strName1 As Variant, strName2 As Variant
Dim caption As String
Dim Prompt As String
 
Prompt = "Enter the Date Range in the 'dd/mm/yyyy' format?"
caption = "Approval Date Range Search"
On Error GoTo Errorline
 
strName1 = DateValue(Application.InputBox(Prompt & " Enter 'START' Date", caption, Type:=2))
strName2 = DateValue(Application.InputBox(Prompt & " Enter 'END' Date", caption, Type:=2))
 
If strName1 = "False" Or strName2 = "False" Then Exit Sub
Range("Table_owssvr_1[Approval Date]").Select
Selection.AutoFilter Field:=6, Criteria1:=">=" & CLng(strName1), Criteria2:="<=" & CLng(strName2), Operator:=xlAnd
Exit Sub
 
Errorline:
ActiveSheet.ListObjects("Table_owssvr_1").Range.AutoFilter Field:=6
End Sub

 

[AlphaFrog]

Try something like this (not tested). Change the name in red to the name of the button this is assigned to.

Private Sub [COLOR="Red"]CommandButton13[/COLOR]_Click()

    On Error GoTo Errorline

    Range("Table_owssvr_1[Approval Date]").Select
    Selection.AutoFilter Field:=6, Criteria1:="<=" & DateAdd("yyyy", -1, Date)
    Exit Sub

Errorline:
    ActiveSheet.ListObjects("Table_owssvr_1").Range.AutoFilter Field:=6
    
End Sub

 

[Craw]

No dice it just filters everything out any other ideas?

 

[AlphaFrog]

    On Error GoTo Errorline

    Range("Table_owssvr_1[Approval Date]").Select
    [COLOR="Red"]Selection.AutoFilter[/COLOR]    [COLOR="Green"]'clear previous filter[/COLOR]
    Selection.AutoFilter Field:=6, Criteria1:="<=" & DateAdd("yyyy", -1, Date)
    Exit Sub

Errorline:
    ActiveSheet.ListObjects("Table_owssvr_1").Range.AutoFilter Field:=6