Date criteria for adv. filter in code

[Lemi]

I have a database in the following pattern:

DATE REMARK DEBIT CREDIT BANK CUST
01.01.09 xyxyxyx A X
-----------------------------------------------------------
10.04.09 jsstmjk B Y
----------------------------------------------------------
10.07.09 sklsjhs A Y


I want to extract the transactions of a certain customer (say X) between two
dates and I want to do it in the code.
My criteria range is like the following:

Columns
5 6 7
Rows 1 DATE DATE CUST
2 >=01.03.09 <=31.05.09 X

Excel do not extract any records although there are several ones matching
those criteria. Why?
If I remove the dates then I get a correct extract for customer X but I
cannot filter it between the given dates.
Is there anything wrong with the punctuation in the cells for dates?

P.S. I don't want to use the advance filter from the menu. It must be dealt
with in the code.

Regards,
Lemi

 

[Patrick Molloy]

please show us your code. Your dates are not actually dates are they? you'll
need to change them to a standard form

 

[Lemi]

Here is the code:

This is a sample code structure of which has been proposed by one of the
Excel authorities in the group.

There are three worksheets: one for the database, second for the criteria
and the last one for the extract of data.

The dates are chosen from the DatePicker controls in UserForm1
-----------------------------
Option Explicit

Dim FirstDate As String

Dim LastDate As String

Sub ExtractUnique_1()

UserForm1.Show

FirstDate = ">=" & UserForm1.DTPicker1.Value

LastDate = "<=" & UserForm1.DTPicker2.Value

With Sheets("Source Database")

.Range(.Cells(1, 1), _

.Cells(.Rows.Count, 11) _

.End(xlUp)).Name = "Database"

End With

With Sheets("Criteria Data")

.Range(.Cells(1, 6), .Cells(1, 8)) = Array("DATE", "DATE", "CUST2")

.Range(.Cells(2, 6), .Cells(2, 8)) = Array(FirstDate, LastDate, "ETUR")

.Range(.Cells(1, 6), .Cells(2, 8)).Name = "MyCriteria"

End With

With Sheets("Output Data")

.Range(.Columns(1), .Columns(11)).Clear

.Cells(1, 1).Name = "MyDestination"

End With

Range("Database").AdvancedFilter _

Action:=xlFilterCopy, _

CriteriaRange:=Range("MyCriteria"), _

CopyToRange:=Range("MyDestination"), _

Unique:=False

End Sub

-------------------------------------

Regards,

Lemi

 

[OssieMac]

Hi Lemi,

VBA does not recognize dates in d/m/y format for AdvancedFilter or
AutoFilter Criteria. Date must be converted to either m/d/y format or use the
literal month as in dd mmm yyyy format.

Because textboxes are text by nature, firstly convert the text date to a
date value and then format to either m/d/y or d mmm yyyy format.

Example 1.
FirstDate = (">=") & Format _
(DateValue(UserForm1 _
.DTPicker1.Value), "mm/dd/yy")

LastDate = ("<=") & Format _
(DateValue(UserForm1 _
.DTPicker2.Value), "mm/dd/yy")


Example 2. (Ossiemac's preferred format because self documenting)
FirstDate = (">=") & Format _
(DateValue(UserForm1 _
.DTPicker1.Value), "dd mmm yy")

LastDate = ("<=") & Format _
(DateValue(UserForm1 _
.DTPicker2.Value), "dd mmm yy")