Missing values in filtered recordset

[briggsy79]

Hi everyone,

This code is designed to get the numer of times different names appear in a recordset, and then filter the list down to the top 10.
At the moment it misses any names that begin
with A and maybe more (one name 'Alby' appears 4 times but is still not present in the filtered recordset)
I havent been able to work out why, any clues? Thanks.

Dim i As Integer
Dim loREcordset As ADODB.Recordset
Dim loField As ADODB.Field
Dim b(1 To 10) As Variant
Set adoRecordset3 = New Recordset
adoRecordset3.Open "Select Name, Count(*) as How_many_times_name_appears from reportertb1 Group by name HAVING Count(*) > 1 order by Count(*)", cn1, adOpenStatic, adLockOptimistic
chtSample.Title = "Name"
Set loREcordset = New ADODB.Recordset
If adoRecordset3.RecordCount >= 10 Then
For i = 1 To 10
b(i) = adoRecordset3.Bookmark
adoRecordset3.MoveNext
Next i
adoRecordset3.filter = b()
With loREcordset.Fields

loREcordset.ActiveConnection = Nothing
loREcordset.CursorLocation = adUseClient
For Each loField In adoRecordset3.Fields
loREcordset.Fields.Append loField.Name, loField.Type, loField.DefinedSize, loField.Attributes
Next loField

loREcordset.Open
End With

With adoRecordset3
.MoveFirst
Do While Not .EOF
loREcordset.AddNew
For Each loField In adoRecordset3.Fields
loREcordset.Fields(loField.Name).Value = loField.Value
Next loField
loREcordset.Update
.MoveNext
Loop

End With
Set DataGrid1.DataSource = loREcordset
Set chtSample.DataSource = loREcordset
chtSample.Refresh
Else
Set DataGrid1.DataSource = adoRecordset3
Set chtSample.DataSource = adoRecordset3
End If

-David

 

[briggsy79]

Okay yes i have the problem, the recordsetis ordered in ascending order.
Which means that if there are more than 10 values above one, the highest
numbers get cut from the group. Does anyone have any idea on how i can
reverse the order of the recordset, Im guess i could filter the recordset
and move from the last record to the first, but is there some syntax i
could put in the sql statement?


-David

 

[CajunCenturion]

you might try

order by Count(*) DESC", Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[briggsy79]

Thanks, that worked

-David