DCount of a Recordset?

  • Thread starter Thread starter dchendrickson
  • Start date Start date
D

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
 
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.
 
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
Click to expand...
accurate count, you may need to
 
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.
Click to expand...

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.
 
Filtered to Null Region: 5 records.

Should read:

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

I guess my debugging was incomplete. <g>
 
but I was wondering if there was something cleaner that
will return the information?
Click to expand...

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
 
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
 
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
 
Back
Top