Counting total records found?

D

Dennis

I need to count the total number of records that were found in a sub-query
(Access 2002). How can I do that please? This isn't in VBA, but is in an
actual Query. Any insight would be most appreciated.

Thanks!
 
O

Ofer Cohen

Well, if you wont get any better answer then, and it does include code

Create a function that return the count of records in the query

Function CountRecords()
CountRecords = DCount("*", "QueryName")
End Function
==================
In the Query use this function to return the number of records, as a new
field
CountOfRecords: CountRecords()

Something I found out recently, thanks to this discussion group, if you
don't pass a parameter to the function it will run it only once, so it wont
run the function for every record
 
O

Ofer Cohen

Well, if you wont get any better answer then, and it does include code

Create a function that return the count of records in the query

Function CountRecords()
CountRecords = DCount("*", "TableName")
End Function
==================
In the Query use this function to return the number of records, as a new
field
CountOfRecords: CountRecords()

Something I found out recently, thanks to this discussion group, if you
don't pass a parameter to the function it will run it only once, so it wont
run the function for every record
 
V

Veritas

Hi Dennis,

How about going to Queries>New>Design View>
Click on the "Queries" tab >
Highlight the query that has the recordset that you want to count records >
click "Add" button

At this point you should see a table that lists the columns of the recordset
of your subquery... if you just want a count of all records in that query...
down below... you should have an area that says
Field: MyCount: Count(*)
Table:
Sort:
Show:
Criteria:
etc.

If you type what I have by Field... you will get a count of all records in
that sub-query...

In place of the *...
if you wanted...
you could also use one of the fields in that table... then if you wanted to
count only the records... where that field has something entered (i.e. count
the records where this field is NOT null...
then in the SECOND column
set
Field: [Field name]

Criteria: Is Not Null

Click on the Red Exclamation mark for your query to run...

OR IN SQL VIEW...
Type...

SELECT Count([System_ID]) AS MyCount
FROM qry_CO
WHERE [System_ID] Is Not Null;

Hope that helps.

Take care,
Veritas
 
Top