Using dates with autofilter
 

Hi, I'm having difficulty filtering a list using auto filter as th
entire list is filtered out even though I know that the dates selecte
are in the list. Could this be a formatting issue?

Firstly, I'm using the calendar control in a user form to obtain th
start date and end dates as follows:

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx x
Public ChosenDate1 As Date
Public ChosenDate2 As Date

Private Sub Calendar1_Click()
ChosenDate1 = Calendar1.Value
End Sub

Private Sub Calendar2_Click()
ChosenDate2 = Calendar2.Value
End Sub

Private Sub cmdAddEndDate_Click()
frmDateRange.txtEndDate = Format(ChosenDate2, "d/m/yy")
frmDateRange.lblInstruction.Caption = "Thank you, now click the 'Ru
Report' button."
End Sub

Private Sub cmdAddStartDate_Click()
frmDateRange.txtStartDate = Format(ChosenDate1, "d/m/yy")
frmDateRange.lblInstruction.Caption = "Now select an end date and clic
the 'Add End date' button."
frmDateRange.Calendar1.Visible = False
frmDateRange.Calendar2.Visible = True
End Sub

Then, I want to filter the list based on the dates but the entire lis
is filtered out. This is the code I'm using:

Private Sub cmdRunReport_Click()
Dim Begin As Date
Dim EndDate As Date

MsgBox ChosenDate1
MsgBox ChosenDate2

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

'Sort the list by item code and then date
Selection.Sort Key1:=Range("J2"), Order1:=xlAscending
Key2:=Range("C2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
DataOption2 _
:=xlSortNormal

'Filter list according to date range selection
Selection.AutoFilter Field:=3, Criteria1:=ChosenDate1
Operator:=xlAnd _
, Criteria2:=ChosenDate2

'Set sub-totals report
Selection.Subtotal GroupBy:=10, Function:=xlSum
TotalList:=Array(13, 30) _
, Replace:=True, PageBreaks:=False, SummaryBelowData:=True


Range("K1").Select
Selection.EntireColumn.Hidden = True
Range("B1").Select
Selection.EntireColumn.Hidden = True
Range("D1").Select
Selection.EntireColumn.Hidden = True
Range("F1").Select
Selection.EntireColumn.Hidden = True
Range("G1").Select
Selection.EntireColumn.Hidden = True
Range("H1").Select
Selection.EntireColumn.Hidden = True

frmDateRange.Hide
End Sub

The dates seem to be fine when I step through the code but while I'
stepping through, if I look at the custom option in the date column I'
trying to filter on, the date in the selection box is in "m/d/yy" forma
and not the "d/m/yy" format set up in the code.

Is this the problem and how do I resolve this? Should I format th
dates to general numbers and convert the dates from the calenda
control to general numbers too?

Thanks for any help offered

--
skuzap
-----------------------------------------------------------------------
skuzapo's Profile: http://www.excelforum.com/member.php...fo&userid=2743
View this thread: http://www.excelforum.com/showthread.php?threadid=52835

Using dates with autofilter
 

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxx


--
skuzapo
------------------------------------------------------------------------
skuzapo's Profile: http://www.excelforum.com/member.php...o&userid=27430
View this thread: http://www.excelforum.com/showthread...hreadid=528356

Using dates with autofilter
 

Did you find a solution to this problem?
--
Adam Thwaites
Access Database Designer

Manchester, UK


"skuzapo" wrote:


Hi, I'm having difficulty filtering a list using auto filter as the
entire list is filtered out even though I know that the dates selected
are in the list. Could this be a formatting issue?

Firstly, I'm using the calendar control in a user form to obtain the
start date and end dates as follows:

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx x
Public ChosenDate1 As Date
Public ChosenDate2 As Date

Private Sub Calendar1_Click()
ChosenDate1 = Calendar1.Value
End Sub

Private Sub Calendar2_Click()
ChosenDate2 = Calendar2.Value
End Sub

Private Sub cmdAddEndDate_Click()
frmDateRange.txtEndDate = Format(ChosenDate2, "d/m/yy")
frmDateRange.lblInstruction.Caption = "Thank you, now click the 'Run
Report' button."
End Sub

Private Sub cmdAddStartDate_Click()
frmDateRange.txtStartDate = Format(ChosenDate1, "d/m/yy")
frmDateRange.lblInstruction.Caption = "Now select an end date and click
the 'Add End date' button."
frmDateRange.Calendar1.Visible = False
frmDateRange.Calendar2.Visible = True
End Sub

Then, I want to filter the list based on the dates but the entire list
is filtered out. This is the code I'm using:

Private Sub cmdRunReport_Click()
Dim Begin As Date
Dim EndDate As Date

MsgBox ChosenDate1
MsgBox ChosenDate2

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

'Sort the list by item code and then date
Selection.Sort Key1:=Range("J2"), Order1:=xlAscending,
Key2:=Range("C2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal

'Filter list according to date range selection
Selection.AutoFilter Field:=3, Criteria1:=ChosenDate1,
Operator:=xlAnd _
, Criteria2:=ChosenDate2

'Set sub-totals report
Selection.Subtotal GroupBy:=10, Function:=xlSum,
TotalList:=Array(13, 30) _
, Replace:=True, PageBreaks:=False, SummaryBelowData:=True


Range("K1").Select
Selection.EntireColumn.Hidden = True
Range("B1").Select
Selection.EntireColumn.Hidden = True
Range("D1").Select
Selection.EntireColumn.Hidden = True
Range("F1").Select
Selection.EntireColumn.Hidden = True
Range("G1").Select
Selection.EntireColumn.Hidden = True
Range("H1").Select
Selection.EntireColumn.Hidden = True

frmDateRange.Hide
End Sub

The dates seem to be fine when I step through the code but while I'm
stepping through, if I look at the custom option in the date column I'm
trying to filter on, the date in the selection box is in "m/d/yy" format
and not the "d/m/yy" format set up in the code.

Is this the problem and how do I resolve this? Should I format the
dates to general numbers and convert the dates from the calendar
control to general numbers too?

Thanks for any help offered.


--
skuzapo
------------------------------------------------------------------------
skuzapo's Profile: http://www.excelforum.com/member.php...o&userid=27430
View this thread: http://www.excelforum.com/showthread...hreadid=528356