DCount of a Recordset?

[dchendrickson]

I am using Access2002/XP Pro and writing code primarily
with ADO structures.

Is there a way to use the DCount() function on a
recordset? (I wrote a little sample procedure and DCount
returns a type mismatch error if a recordset variable is
used as the domain.)

I have a recordset that contains two types of records -
the number of each type is unknown. I would like to know
how many of the total records belong to each type. I know
I can walk the recordset and keep track of things myself,
but I was wondering if there was something cleaner that
will return the information?

Thanks,

-dc

 

[Wayne Morgan]

Try the RecordCount property of the Recordset. To get an accurate count, you may need to
do a MoveLast first to fully populate the recordset.

 

[Guest]

Wayne,

Thanks for the suggestion, but doesn't RecordCount return
the count of ALL the records in the recordset? I want to
be able to perform a count with a WHERE type criteria...
like DCount, but on a recordset I have opened.

Thanks,

-dc

-----Original Message-----
Try the RecordCount property of the Recordset. To get an

accurate count, you may need to

 

[Bruce M. Thompson]

Thanks for the suggestion, but doesn't RecordCount return

the count of ALL the records in the recordset? I want to
be able to perform a count with a WHERE type criteria...
like DCount, but on a recordset I have opened.

In ADO, you can define a filter on the recordset and *then* do a "RecordCount":

'****EXAMPLE START
Public Sub subCountFilteredRecordsADO()
' Comments : Based on "Employees" table in Northwind.mdb
' Created : 11/12/03 21:02 Bruce M. Thompson
' --------------------------------------------------

On Error GoTo subCountFilteredRecordsADO_ERR

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

Set cnn = Application.CurrentProject.Connection
rst.Open "Employees", cnn, adOpenKeyset, adLockOptimistic

Debug.Print "Not Filtered: " & rst.RecordCount & " records."

rst.Filter = "Region = 'WA'"

Debug.Print "Filtered to Null Region: " & rst.RecordCount & " records."

subCountFilteredRecordsADO_EXIT:
On Error Resume Next
rst.Close
Set cnn = Nothing
Exit Sub

subCountFilteredRecordsADO_ERR:
MsgBox "Error " & Err.Number & " occurred in subCountFilteredRecordsADO: " &
Err.Description
Resume subCountFilteredRecordsADO_EXIT

End Sub
'****EXAMPLE END

From the debug window, after running the preceding procedure:

Not Filtered: 9 records.
Filtered to Null Region: 5 records.

 

[Bruce M. Thompson]

Filtered to Null Region: 5 records.

Should read:

Filtered to "Region = 'WA'": 5 records.

I guess my debugging was incomplete. <g>

 

[Tim Ferguson]

but I was wondering if there was something cleaner that
will return the information?

Get the original SQL and convert it into a COUNT() grouping query


SELECT RecType, COUNT(*) AS NumberOfRecords
FROM MyTable
GROUP BY RecType
ORDER BY RecType;


If you have created a recordset, then you must know what the SQL is, right?

B Wishes


Tim F

 

[Guest]

Bruce,

It amazes me how sometimes the obvious is so hard to see -
sort of a forest/trees issue. Thanks so much for the
idea - I haven't coded it yet, but it looks like just
what I wanted.

-dc

 

[Guest]

Tim,

Just think of me as the "Gilligan" of SQL! I have taught
myself to write SQL by looking at how Access does it. I
can bully my way through a SELECT. But all the other
capabilities - in this case a wonderful keyword like
COUNT - usually escape me.

Thanks for aiding and abetting my SQL education.

-dc