Search by approval date coding issue

[Craw]

Greetings,
I am trying to get this code to work. It works when i'm searching for text but I can't seem to get it to search for approval dates (dd-mm-yyyy) that are in a spreadsheet i created.

Private Sub approvalsearch_Click()

Dim strName As String
Dim caption As String
Dim Prompt As String

Prompt = "What Approval Date Would You Like to Search For?"
caption = "Approval Date Search"
strName = Val(InputBox(Prompt, caption))

Range("Table_owssvr_1[Approval Date]").Select
Selection.AutoFilter Field:=6, Criteria1:="=*" & strName & ", Operator:=xlAnd"

End Sub

Any ideas on how to fix it?

 

[Craw]

Nevermind I got it to work I was missing the last line of code it didnt copy for reason. Once again my hat is off to you.

Thanks Much,

Craw

 

[Craw]

Well I got the code to work after all thank you for all your help. But now I have one last question. The code I used to get the Date Range subroutine to work is as follows:

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 Resume Next

strName1 = DateValue(InputBox(Prompt & " Enter 'START' Date", caption))
strName2 = DateValue(InputBox(Prompt & " Enter 'END' Date", caption))

Range("Table_owssvr_1[Approval Date]").Select
Selection.AutoFilter Field:=6, Criteria1:=">=" & CLng(strName1), Criteria2:="<=" & CLng(strName2), Operator:=xlAnd

End Sub

How would I code this to use an application inputbox so that when the cancel button is hit the command is ended without filtering any data and if the user leaves the inputbox blank the command will look for blank values. As some of the documents in my last have not been approved and searching for blank columns would be helpful.

Best Regards,


Craw

 

[VoG]

Maybe

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 Resume Next

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

End Sub

 

[Craw]

if nothing is entered it just filters everything out and no values are shown so I have to clear the filter. Is there a way to at least make it clear the filter on error?

the filter clearing code I have is as follows:

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

I'm just not sure where to put it. I have tried putting it after the error handling command but then it still filters everything out?

 

[Craw]

Actually is there a way to make the errorline I have added be skipped if their is no error?

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

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

End Sub

 

[VoG]

Try

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

 

[Craw]

Brilliant thank you

 

[Craw]

I have a very similar issue with some of my other functions. I would like to arrange it so that if the user does not enter a value or hits cancel it does not filter the autofilter by 0 rather that it clears the filter automatically. Any suggestions?

Private Sub CommandButton10_Click()

Dim strName As String
Dim caption As String
Dim Prompt As String

Prompt = "Enter keyword"
caption = "Keyword Search"

strName = (InputBox(Prompt, caption))
Range("Table_owssvr_1[Keywords]").Select
Selection.AutoFilter Field:=5, Criteria1:="=*" & strName & "*", Operator:=xlAnd

End Sub

 

[VoG]

Maybe like this

Private Sub CommandButton10_Click()

Dim strName As String
Dim caption As String
Dim Prompt As String

Prompt = "Enter keyword"
caption = "Keyword Search"

strName = (InputBox(Prompt, caption))
Range("Table_owssvr_1[Keywords]").Select
If strName = "" Then
    Selection.AutoFilter
Else
    Selection.AutoFilter Field:=5, Criteria1:="=*" & strName & "*", Operator:=xlAnd
End If
End Sub

Please use Code Tags when posting code

[code]
your code here
[/code]

 

[Craw]

Hey,
I would like to modify this code for another function 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 CommandButton5_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