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?

 

[VoG]

Try this

Private Sub approvalsearch_Click()

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

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

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

End Sub

This is very similar to your previous thread and you should really have continued there. Please see rule #9 here: http://www.mrexcel.com/forum/showthread.php?t=99490).

 

[Craw]

My apologies, that code you suggested did not work but I will look at it again.

Regards,

Craw

 

[Craw]

I just can't seem to get that code to work and the cells its accessing are already formated as dates but they show up as dd/mm/yyyy.

 

[VoG]

Dates can be a pain to work with in VBA. Maybe

Private Sub approvalsearch_Click()

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

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

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

End Sub

 

[Craw]

Thank you peter it worked like a charm. I was wondering if it would be possible to use the same code but to make it search for dates withing a range? The user would be prompted by an inputbox to input the dates between which he would like to search.

Thanks again,

Craw

 

[VoG]

Maybe like this

Private Sub approvalsearch_Click()

Dim strName1 As Variant, strName2 As Variant
Dim caption As String
Dim Prompt As String

Prompt = "What Approval Date Would You Like to Search For?"
caption = "Approval Date Search"
strName1 = DateValue(InputBox(Prompt & " FIRST DATE", caption))
strName2 = DateValue(InputBox(Prompt & " LAST DATE", caption))

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

End Sub

 

[Craw]

The code seems to filter out the items that fit between the dates I am looking for and instread leave only the rows that have a blank in the approval date column. Is there a way to filter out the approval column cells that are blank?

Best Regards,

Craw

 

[VoG]

Try like this

Selection.AutoFilter Field:=6, Criteria1:="="

 

[Craw]

No dice any other ideas?