Date criteria for adv. filter in code
 

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

Date criteria for adv. filter in code
 

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

"Lemi" wrote in message
...
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

Date criteria for adv. filter in code
 

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



"Patrick Molloy" wrote in message
...
please show us your code. Your dates are not actually dates are they?
you'll need to change them to a standard form

"Lemi" wrote in message
...
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

Date criteria for adv. filter in code
 

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")


--
Regards,

OssieMac


"Lemi" wrote:

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



"Patrick Molloy" wrote in message
...
please show us your code. Your dates are not actually dates are they?
you'll need to change them to a standard form

"Lemi" wrote in message
...
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