Count Records
By Richard Rost
3 years ago
Count Records in a Continuous Form with Criteria
In this Microsoft Access tutorial, I'm going to teach you how to count the number of records in a continuous form and also apply criteria. For example, if you want to count the number of customers from Florida, you can obtain that count in the form footer using the Count function.
Daniel from Burbank, California (a Gold Member) asks: I'm trying to get a count of customers from my home state of California on my customer list. I've attempted several different formulas following your Form Footer Totals video, but I just can't seem to get it to work. For example, I've tried: =Count([State]="NY") and it doesn't work. Please help.
Members
Members will learn how to create a combo box to select the state from a list of states that exist in the customer table. They can then filter the count results based on that state, allowing for on-the-fly changes. We will use one line of Visual Basic code.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Usage
- =Sum([State]="NY")*-1
- =Count(IIf([State]="NY",1,Null))
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, ms access count records in continuous form with criteria, Count Records in a Continuous Form, count function with criteria, count function
Subscribe to Count Records
Get notifications when this page is updated
Intro In this video, we will learn how to count records in a continuous form in Microsoft Access using specific criteria, such as counting customers from a certain state. I will show you different methods using the Count and Sum functions in the form footer, explain why certain formulas may not work as expected, and discuss the use of the Iif function to improve accuracy. We'll also talk about the DCount function and why it might not be the best option for large datasets in this scenario.Transcript In today's video, we're going to learn how to count the number of records in a continuous form with criteria in Microsoft Access.
Today's question comes from Daniel in Burbank, California, one of my Gold members. Daniel says, I'm trying to get a count of customers from my home state of California on my customer list. I have attempted several different formulas following your form footer totals video, but I can't seem to get it to work. For example, I've tried =Count(State="New York") and it doesn't work. Please help.
For the rest of you, if you have not yet watched my form footer totals video, go watch this first. I explain how to use the Sum function to get totals down here. You can total up the quantity, total up the price. There is a very similar function called Count. There is also Max, Min, and a bunch of other ones. You can use these to put totals or counts down here in your form footer. Go watch this first and come on back if you haven't seen it.
I consider this an expert-level video, which means it is beyond the basics, but it is not quite developer. We do not need any programming for today. This is material I cover in my Expert classes because we're using some functions down here in the form footer.
Here I am in my TechHelp free template. This is a free database you can download from my website if you want to. Here, I've got a customer list, which is a continuous form. What Daniel is trying to do is count up the number of customers he has from California.
Let me sort this list so we can see here. I don't have any from California. I'll use my home state of Florida. I've got four people in here from Florida.
To put a count down here, it's pretty straightforward. We're going to go into Design View. I'm just going to copy the state box, copy, click it down here in the form footer, and paste. Then we'll just slide this over here. I don't want it bound to State. I don't want the state from the table appearing there. We're going to get rid of the control source for now.
Let's name this Count or let's call it StateCount. StateCount. Now for the control source, this is where we're going to put our function. It's going to be =Count(*). Just to get a complete count of all the records, you'd put a star in there.
Let me zoom in so you can see that better. Shift F2. I'm going to put in there to count all the records. Hit Save, close it. Let's close it and reopen it. There's 33 records that matches this. 33 records. We're good.
If I come in here and go to Design View and go back in and instead of count all the records, I'm going to put in here what Daniel had: Count(state="Florida"). Save it, close it, close it, open it back up, and I get a 28. Why am I getting 28?
Well, the problem is that the Count function doesn't really care about what your criteria is. It just cares about getting a true or false value back. If State="New York" is true, that's going to return a true value, which is negative 1 in Access. That will add to the count. But if it returns a false, let's say you got New Deal with Hawaii, that's a zero. That's also a record.
Count just counts up the number of records. It doesn't really care what the criteria is. In this case, we're getting a 28 because we've got five records up here that have null values in there and null values do not add to the count. So basically, it's saying that it doesn't care what your criteria is. You're getting a count of one for any value, true or false. The only thing it doesn't count up is null values.
If you know the Iif function, and if not, here's a video you can go watch. I'll put this down in the link section below. You could say, if you know the Iif function, right in here, I want to count if the state equals Florida, give me a one. Otherwise, do not give me a zero because zero is also going to add to the count, give me a null. If the state is Florida, give me a one. Otherwise, give me a null, which will not add to the count.
Save it, close it, close it, and there's your count of four. That's one way to do it if you want to really stick to the Count function, but actually, there's an easier way to do it. If we keep in mind that State="Florida" will return a true or false value, negative one or zero, then instead of counting those, we can add those up with the Sum function like this.
Design View. Open her back up again. Instead of saying Count(State="Florida"), we're going to Sum(State="Florida"), just like that.
Save it, close it, close it, open it. Oh, look at that. I got a negative four. Why is that?
Because this one returned true, this one returned true, these four returned true, the rest of them returned false, which is zero. Technically, these return null, but that's okay. We don't care about that. In Access, true is negative one, not one like it is in some other databases, like SQL Server. Just remember that. In Access, true is negative one.
Now we have the sum of those values, State="Florida". All we have to really do here is just multiply the result times negative one. Hit OK, save it, close it, close it, open it, there's your answer. That's the easiest way to do it with the Sum function.
You could stick with Count if you want, but then you have to use the Iif. The thing to remember is that Count just counts the records that are returned. It doesn't really care what your criteria is. Sum will listen to your criteria better.
Now for those of you who know DCount, and if not, I have a whole separate video you can watch on this. DCount you can use to look inside any table or query and count up the records. There is DCount, DSum, DMax, DLookup. I've done a lot of videos on these.
DCount and all these are domain aggregate functions. These are great if you're on a single form, and you want to go look somewhere else to count stuff up. Like in this example here, it can take the single customer form and count the number of orders that this customer has from the order table, a different table.
Unfortunately, if you're dealing with thousands and thousands of records, this can be really slow. In this table, we don't want to use DCount because it's going to have to go out to the customer table and count all those records up again. That's two sets of counts, two sets of loading up all those records. We've already got the records loaded up in this table. Just count what's there. That's why we don't want to use DCount here. We could, and it will work, but I'd rather not, especially if you get 100,000 records in this table.
I'm just checking for Floridians here, which is really all Daniel wanted. We want to check for people in California. Maybe you want to; those are the people that you've got to charge sales tax to, whatever the reason is.
What if you want to change that easily on the fly? That's what we're going to cover in the extended cut for the members. I'm going to put a little combo box out here showing just the states that are in your customer table. So you don't see all 50 of them, you just see the ones you've got in the table. If you've got four, you'll just see four down here. That will be covered in the extended cut for the members.
Remember, as an upgrade, you get access to all of my extended cut videos, Gold members can download these databases, and you can access the Code Vault. Plus, all my members get a free class every month.
If you're interested in learning how to do this, check it out. It involves a tiny bit of programming, literally one line of programming. If you want to learn more about these form footer totals, I cover them in detail in my Access Expert Level 3 class. We go over all kinds of stuff, all the Sum, Average, Max, Min, Count, all that kind of stuff. Check it out. I'll put a link to this down below as well.
That is going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is the main goal Daniel wants to achieve in his Access form?
A. Count the number of customers from a specified state
B. Total up the price of all customer orders
C. Find the record with the highest quantity
D. Display the list of all unique customer states
Q2. What function does Daniel initially try in Access to count records with a criteria?
A. =Sum(State="California")
B. =Count(State="New York")
C. =Max(State="California")
D. =Min(State="New York")
Q3. What is the problem with using =Count(State="Florida") as a control source to count specific state records?
A. It ignores null values and criteria, counting every non-null record
B. It causes Access to crash
C. It only totals numeric fields
D. It returns a negative count
Q4. In Access, what numeric value does a TRUE Boolean expression return?
A. 1
B. 0
C. -1
D. 100
Q5. Which function provides a more accurate count of records that match your criteria by evaluating the condition for each record?
A. Min
B. Max
C. Sum
D. Average
Q6. When using the Sum function to total Boolean values like State="Florida", what adjustment is needed to get the positive count?
A. Multiply the result by zero
B. Add one to the result
C. Subtract one from the result
D. Multiply the result by negative one
Q7. Why is it generally better to use Sum rather than Count with an Iif or criteria for this task?
A. Sum is easier to write and interprets Boolean results directly
B. Count is faster with large tables
C. Sum ignores null values, which reduces errors
D. Count cannot be used in continuous forms
Q8. What is the purpose of the Iif function in the context of counting records with a criteria in Access?
A. It multiplies all values by negative one
B. It ensures only records that meet the criteria contribute a non-null value to the count
C. It displays results as percentages
D. It sorts records by state
Q9. What is the main downside of using DCount for this purpose in a continuous form with many records?
A. It only works for numeric data
B. It can be slow because it loads the data twice
C. DCount does not accept criteria
D. It automatically includes null values
Q10. In Access continuous forms, why is it unnecessary to use DCount for counting visible records?
A. DCount ignores the visible filter
B. The data is already loaded, so using Sum or Count is more efficient
C. DCount cannot be used in form footers
D. DCount requires VBA code to function
Q11. If you want to allow the user to change the counted state on the fly in the form, what technique was suggested for members in the extended cut?
A. Use a combo box with available states in the customer table
B. Manually filter the data each time
C. Edit the Sum function in every record
D. Use a text box for entering state names
Answers: 1-A; 2-B; 3-A; 4-C; 5-C; 6-D; 7-A; 8-B; 9-B; 10-B; 11-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone covers how to count the number of records matching specific criteria in a continuous form in Microsoft Access. This lesson was inspired by a question about counting customers from a particular state, such as California, on a customer list. If you are unfamiliar with adding totals in a form footer using functions like Sum, it's recommended to review my earlier form footer totals tutorial where you can learn about summing and other aggregate functions.
This tutorial is intended for users with some experience in Access, especially those who have worked with functions in form footers before. There's no programming or VBA required for this solution.
In my demonstration, I'll use my TechHelp free template and focus on a continuous form listing customers. The goal is to determine how many customers are from a particular state - for example, Florida - directly within the form's footer.
To accomplish this, the process starts in Design View. If you want to display a count in the footer, you can copy an existing text box for the state, paste it into the form footer, and clear its control source. Assign it a new name, such as StateCount.
To start with a simple record count, set the control source to use the Count function with an asterisk, which will count all records in the form. This approach will simply show the total number of records displayed by the form.
However, if you need to count only records that meet a certain criterion, such as "state = Florida", a common mistake is to try using Count(State="Florida"). While it may appear to work, Count only considers whether there is a value in a field - it doesn't evaluate the logic of your criteria. In Access, a boolean expression like State="Florida" returns True (represented as -1 in Access) or False (0), but Count just checks for non-null values and ignores the actual boolean result. As a result, you might get misleading results if there are any null values.
A better approach is to use the Iif function if you want to stay with Count. You can write an expression that instructs Access to output 1 if the criterion is met, otherwise output Null. In this way, only records meeting your condition are included in the count, since Count ignores Null values.
An even easier and more efficient method involves using the Sum function. Since State="Florida" returns -1 for matches and 0 for non-matches, you can sum the results of the boolean expression across all records. The result will be negative, as True in Access equates to -1. To get a positive count, simply multiply the result by -1.
For example, using Sum(State="Florida") will give you -4 if there are four matching records, so multiplying by -1 produces the desired positive count.
It's also worth discussing DCount, which is a domain aggregate function. DCount allows you to count records that meet particular criteria anywhere in your database, even in other tables or queries. While DCount is powerful, it is not the best tool for this scenario, especially when dealing with large data sets and you already have the relevant records loaded in your form. Using DCount in this context would be inefficient, as it would perform an extra lookup in the data source.
If you would like to make the counting criteria more dynamic, such as being able to select different states easily, I cover how to add a combo box that displays only the states present in your data. This feature and its implementation, including the small amount of code required, are discussed in the Extended Cut video available for members.
For those who want to learn more about working with form footer totals, including average, maximum, minimum, and count functions, I go into these topics in depth in my Access Expert Level 3 class.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Counting records in a continuous form in Access
Using the Count function in a form footer
Limitations of Count with criteria in Access forms
How null values affect Count results
Using the Iif function with Count for conditional counting
Using the Sum function to total boolean conditions
Adjusting for negative values when summing boolean expressions
Difference between true values in Access and other databases
Explanation of domain aggregate functions like DCount
Why to avoid DCount for counting records already loaded in a form |
