applied tips : march 2008

microsoft access : counting records in forms and reports

Use expressions to count the number of records

Even novice Access users are aware that the navigational toolbar is an easy way to find out not only the number of the record that’s currently open, but also the number of records in the current recordset. For example, if you are looking at record 25 in a table that has 254 records, the navigational toolbar would tell you that you are on record 25 of 254.  Of course none of this data is available to us on a report, and sometimes we would like to see it.

Oftentimes, this basic bit of information is all we need to know. But there may be times when you want to have more than just a simple count of records in the current form. 

This article will show you how to use two different form (or report) controls to count the records in a form, table, or query and display them in a form or report.  Since the process is the same for both forms and reports, I'll just say forms from now on.

Expression Controls

First you'll need to understand that we are creating a new control (a text box) for our form.  Create the text box while in Design Mode of your form using the toolbox.  Place it where you would like it to appear and format it to your preference.  If you were creating a report, you might want this in the Report Footer or a Section Footer.  You might even want a little horizontal line just above it.

Method 1: Count

This expression evaluates the entire group of records in the active form or report. There are two ways to use this function, depending on how you want to count the records:

=Count(fieldname)

When you specify a field, you are specifying the group of records to be included in the count. However, when a field is referenced, Null entries are not included in the count.

=Count(*)

Use this expression to count the total number of rows being evaluated, including those with Null entries.

The disadvantage of using the Count expression is that it can be annoyingly slow in forms, especially if thousands of records are being evaluated. Patience is necessary, because if you don’t want until all the records are counted, you will have an inaccurate number. It is also inefficient for forms because it recalculates the records each time you move between them.  In a report, it's perfectly fine.

Method 2: DCount

This is the most flexible of the two methods because it can tally up records from anywhere in the database - not just the current recordset.

Just like before, create a textbox and set the source to our expression.  We'll use this example to start:

=DCOUNT("[EmployeeID]","[Employees]","[Active]=True")

The above expression will look into the Employees table and return a tally of all employees whose Active field is true.  It has nothing to do with the form or report you may have loaded, but can be pretty useful to see from time to time.

Note that you don't have to have static criteria, either.  You might want something like this:

=DCount("[ClassID]","[Certificates]","[ClassID] = " & Forms!formname.ClassID)

In this case, we're looking at the current form (formname) for a control called ClassID, and then looking for that value in the Certificates table and returning the number of certificate records with a matching ClassID.

You can see it can get pretty complicated, but also very powerful.

Method 3: RecordSet

There is a third method to counting records, and it's a lot more efficient and flexible than the other two. However, it involves some VBA coding and is beyond the scope of this column.  If you're interested, let me know and I'll try to help you out.

schedule a class or get consulting on access

Request an on-site Access class from Applied Office or get some helpful consulting. Sessions are just $125/hr.  Learn more here

quick reference card

Get the Quick Reference Card on Microsoft Access! Download it for free and print it on your own printer. You might even want to laminate it.