How to SKIP in recordset?

Hi all, I am getting records from a table and display it in excel file BY MONTH. My problem is I read the recordset until EOF and I SELECT DISTINCT DATE FROM REPORT ... so it will display duplicate date in excel because it loop through the whole record set. UNLESS the date and exactly the same to other, it will stays in the same header. Else, it will display repeatedly in other header. My question is ... how do I skip recordset after I have done first recordset process? Like skip the rest which have the same MONTH as the previous processed records?
Code as below:-

======================================

.........

Dim strRow As Integer
Dim RowNumber As Integer

RowNumber = 5
strRow = 4

getData = "SELECT DISTINCT DATE FROM REPORT"
Set rs = cn.Execute(getData)

Do While Not rs.EOF

If rs.Fields("date") >= dtStartDate And rs.Fields("date") <= dtEndDate Then


'found = True

Dim pass As String
pass = rs.Fields("date")

Dim findArray() As String

findArray = Split(pass, "/")

'this is just to count how many items in each month
Dim getReport4 As String
Dim rs4 As New ADODB.Recordset

getReport4 = "SELECT COUNT (month(DATE)) AS TEMP FROM REPORT WHERE month(DATE)='" & findArray(0) & "'"
Set rs4 = cn.Execute(getReport4)

Dim temp1 As Integer

temp1 = rs4.Fields("TEMP")

' MsgBox "Date = " & rs.Fields("date") & " month = " & findArray(0) & " has counted " & temp1

getReport2 = "SELECT * FROM REPORT WHERE month(DATE)='" & Month(rs.Fields("date")) & "'"
Set rs2 = cn.Execute(getReport2)

If (strRow > 4) Then

.Cells(strRow - 1, 1).Value = "Date" ' cells (2, 1) means row 2 and column 1
.Cells(strRow - 1, 6).Value = "Bank Name"
.Cells(strRow - 1, 3).Value = "Bank Type"
.Cells(strRow - 1, 4).Value = "Amount $"
.Cells(strRow - 1, 5).Value = "Deposit / Withdrawal"
.Cells(strRow - 1, 2).Value = "Category"
.Cells(strRow - 1, 7).Value = "Comments"

'Header settings

.Rows(strRow - 1).HorizontalAlignment = xlHAlignCenter ' set header in center
.Rows(strRow - 1).Font.Bold = True ' set first row - header as font bold
.Rows(strRow - 1).Font.ColorIndex = 5 ' set header words in blue
.Range(.Cells(strRow - 1, 1), .Cells(strRow - 1, 1)).RowHeight = 30
.Range(.Cells(strRow - 1, 1), .Cells(strRow - 1, 7)).Interior.Color = RGB(180, 180, 180)

Do While Not rs2.EOF


'set the string equal to the row number to start on
strRow = "" & RowNumber & ""
.Range("A" & strRow) = Format(rs2.Fields("date"), "Medium Date")
.Range("F" & strRow) = rs2.Fields("bank_name")
.Range("C" & strRow) = rs2.Fields("account_type")
.Range("D" & strRow) = Format(rs2.Fields("amount"), "currency")
.Range("E" & strRow) = rs2.Fields("deposit_withdrawal")
.Range("B" & strRow) = rs2.Fields("category")
.Range("G" & strRow) = rs2.Fields("description")

'auto fit columns
.Range(.Cells(4, 1), .Cells(strRow, 8)).Select
xlsApp.Selection.Columns.AutoFit

rs2.MoveNext
RowNumber = RowNumber + 1
Loop

'set 2 blank rows between new banks
RowNumber = RowNumber + 3
strRow = RowNumber

Else


.Cells(strRow, 1).Value = "Date" ' cells (2, 1) means row 2 and column 1
.Cells(strRow, 6).Value = "Bank Name"
.Cells(strRow, 3).Value = "Bank Type"
.Cells(strRow, 4).Value = "Amount $"
.Cells(strRow, 5).Value = "Deposit / Withdrawal"
.Cells(strRow, 2).Value = "Category"
.Cells(strRow, 7).Value = "Comments"

'Header settings

.Rows(strRow).HorizontalAlignment = xlHAlignCenter ' set header in center
.Rows(strRow).Font.Bold = True ' set first row - header as font bold
.Rows(strRow).Font.ColorIndex = 5 ' set header words in blue
.Range(.Cells(strRow, 1), .Cells(strRow, 1)).RowHeight = 30
.Range(.Cells(strRow, 1), .Cells(strRow, 7)).Interior.Color = RGB(180, 180, 180)

Do While Not rs2.EOF

RowNumber = 5
'set the string equal to the row number to start on
strRow = "" & RowNumber & ""
.Range("A" & strRow) = Format(rs2.Fields("date"), "Medium Date")
.Range("F" & strRow) = rs2.Fields("bank_name")
.Range("C" & strRow) = rs2.Fields("account_type")
.Range("D" & strRow) = Format(rs2.Fields("amount"), "currency")
.Range("E" & strRow) = rs2.Fields("deposit_withdrawal")
.Range("B" & strRow) = rs2.Fields("category")
.Range("G" & strRow) = rs2.Fields("description")

'auto fit columns
.Range(.Cells(4, 1), .Cells(strRow, 8)).Select
xlsApp.Selection.Columns.AutoFit

rs2.MoveNext
RowNumber = RowNumber + 1
Loop

'set 2 blank rows between new banks
RowNumber = RowNumber + 3
strRow = RowNumber
End If

End If

rs.MoveNext
pass = ""

Loop

...............

hey HB - let me try this one...

instead of declaring a new recordset, and then working with two of them... why don't you try this...

once you have your recordset, and you know what month you're working with, apply a filter like so



That should find all records where your month is the same, move through them, and count how many there are... then it will move to the first one, and then position itself on the last recordset WITHOUT the filter. All of this is not necessary if you pass the recordset to a public function by reference, and then just loop through until the month is not equal.

Hi FM ... there is no "addfilter" function in recordset properties ... right? I got error message for this

addfilter??Ido't find it too..U can use sql statement according to the month sort..First diaplay 1th Month result, and add to displaying 2th Month result...

guys.. I'm sorry.. I must have been smoking crack when I wrote that... it should be the following

This is right! Thx Fisherman!