Count Occurrences
By Richard Rost
4 years ago
Count # of Substring In String in Microsoft Access
In this video, I'm going to teach you how to count the instances of one string occurring inside another string. For example if the big string is "Hello there. How are you today?" and you want to count the number of times the letter "o" appears, you'll get a result of 4.
Pre-Requisites
Links
Recommended Courses
 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, Access count substring in string or text, count occurrences of a character inside a string, Counting specific chars in a string, Count the Number of Occurrences of a String
Intro
In this video, I will show you how to count the number of times one string appears within another string using Microsoft Access and VBA. We'll look at both the traditional loop-based approach and a more efficient method using string replacement and character counting. I'll walk you through creating the CountOccurs function, discuss how to handle null values with variants, and demonstrate using this function in a query to analyze your database fields. This fast tip is geared towards Access developers but includes all the details you need to follow along, even if you're new to VBA.
Transcript
Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.
In today's video, I'm going to show you how to count the occurrences of one string inside another string. So if you have a big string like "Richard is super awesome" and you want to see how many times "Richard" appears in there, you'll get a one. Or "Tasha was killed by Black goo. Black goo killed Tasha." If you want to find the "Tasha"s, you'll get a two. So count the number of times a string or just a character appears inside that string.
This is a developer-level fast tip. What does that mean? That means you're going to need to know a little bit of VBA to understand what I'm talking about. If you don't know VBA, if you've never done any VBA programming before, don't be scared of it. This video teaches you everything you need to know to get started and it's about 20 minutes long. Just watch this and then come on back.
If you're thinking to yourself that you might have seen me cover this topic before, you would be correct. I actually covered this topic way back about two years ago in my For Next Loops video where I showed you how to do a For Next loop. In that video, I'm going to take you inside the Code Vault. In that video, we built something similar to this. I used a brute force approach to figuring out the count of the occurrences. There is nothing wrong with this approach. It works fine. I wrote this code probably 10 years ago and it's been working fine for me ever since. Never had a problem with it.
Basically, it takes string one, string two, and writes s and c. It checks to see if they're null or empty strings, which technically the is null would never work because you can't pass a null value if that's a string. We'll talk about that in a minute. You'll get an error with that, which I never had happen before. We simply set the counter to zero, loop from x equals one to the length of the string, and then just see if the mid of that string at that location is equal to c, the smaller string, then increment your counter and continue on, loop through the whole thing. Again, this works just fine.
I always have a brute force approach in my brain when I first tackle a problem. I always think in terms of loops and things like that because that's how I was trained as a programmer, doing old BASIC stuff. For loops and that kind of thing.
Sometimes you don't always think that there's a more elegant solution available to maybe just use a little math. I came across this concept, and after doing a little bit more research, I figured out that this is probably a better way to do it.
What you essentially do is you just replace the small string with an empty string inside the big string. Count the difference in the number of characters between those two strings, then divide by the size of that small string, and that will tell you how many of those small strings occur in the big string.
So if I have a string like this and I want to count the number of R's in it: "My name is Richard Rost." If I start here and I loop through it, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, there's an R, count that one. That's the brute force approach.
But if I simply take this string and I replace the R's with nothing, with blanks, an empty string, then count that string, I'll see there's two characters less. Divide that by the length of the initial string, which is 1, and you can tell right from that what the count is.
Same thing here with the "Black Goo." Do the same thing, replace "Tasha" with an empty string, replace it there with an empty string, count how many characters different you have, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Ten divided by five, which is the length of "Tasha," is two. There are two of those in that string. Lot more elegant than a brute force approach and it runs a lot faster too.
You're not going to notice the speed on your average computer, but if you're doing a big, big query with tens of thousands of records and you have to run that on all of them, it could make a noticeable difference in speed.
Here's the function. Gold members, you'll be able to go to the Code Vault and just hit copy. The rest of you can type and pause the video. There you go. Another reason to upgrade to gold.
Now that I have that code copied to my clipboard, I can go to my TechHelp free template. You can go to a global module. If you don't have one, if you have an older version of the template, or you're using your own database, go to Create Module (not class module, just module), and then come in here and go into there. There it is. We'll just paste that down here in the global module that comes with the TechHelp template.
Now, I added the Sleep function. I've been using the Sleep function in a lot of my videos, so I just decided to put it in there. But there's my CountOccurs.
Now I can save that and we can throw this in a query. We can use it in a form. We can put it anywhere you want.
Let's put it in a query. Create query design. I'm going to bring in my Customer table. Let's see how many times the FirstName field occurs inside the Notes field. Notes will be our big string and FirstName will be our little string. We can come right over here. I'll zoom in.
We'll say C is CountOccurs, the name of the function, notes, comma, first name. If you want to put something in here like "R," if you want to count the number of R's or exclamation points, you can put an actual string in there, but I'm going to use two fields like that.
This, by the way, is also why I switched this to variant instead of string, because if these are strings and you try to pass a null value into it, then you'll get an error. Your query will show #Error all over the place and you don't want that. No one wants to see #Error. So at least now it'll just return zero. It'll say if isnull(s) or isnull(c), then it'll exit the function with the value of zero. You can only do that with variants. You can't do that with string values.
Let's save this. I'll just call this "MyCountOfWhateverQ" and then we'll run it. I can see Richard already appears in there. I'm just going to copy this guy here. Copy. Where's Tasha? We'll paste that in there and there's two. The most disappointing death in all of science fiction, I think. I wouldn't have chosen "killed by the black goo."
There you go. There's your fast tip for today and a little walk down memory lane for my For Next video. Go watch it if you want to. It's still a good example to use for For Next loops. I'm going to keep that old video around. It's a good video, and like I said, that code works great. I've been using it for years.
But this also shows you that a lot of times, if you want to look at ways to speed up your database, take a look at things that you're doing iteratively like that. Try to say that ten times fast. Iteratively, like counting loops and things like that. Sometimes there's a faster way to process data than to just use loops all the time.
These are easy. I'll often program a solution with a loop first, and then if things are running slow, I'll see how I can optimize that. I learned BASIC programming on my old Coco, TRS-80, back when I was eight. My brain tends to think in terms of loops. I'm a big For Next loop guy or while loops.
After that, I was a C programmer. I learned SQL many years later. I was probably in my twenties when I first learned SQL. Sometimes I don't always think of a solution. A lot of things you can do, I try to think of iteratively with a record set looping through records versus letting the server do the work within an SQL statement. So I have to go back and say to myself, I have this working now with VB. Can I accomplish the same thing better and faster with SQL? That's one of my challenges. I'm always learning and always growing as a developer myself. I learn things too almost every day. I am very disappointed if I don't learn something every day.
That's it. There's your fast tip for today and a bit of pontificating from yours truly. I hope you learned something. I know I did. We'll see you next time.
Quiz
Q1. What is the main purpose of the CountOccurs function discussed in the video?
A. To count the total number of words in a string
B. To count the number of times a smaller string appears within a larger string
C. To find and replace strings within a database
D. To remove whitespace from a string
Q2. What was the original, "brute force" method for counting string occurrences?
A. Using SQL COUNT queries directly
B. Using a For Next loop to scan through the string and check for occurrences
C. Using built-in Access functions only
D. Using arrays to split and count segments
Q3. What is the advantage of the newer method of counting string occurrences discussed in the video?
A. It allows for counting with regular expressions
B. It sorts the strings before counting them
C. It replaces the small string with an empty string and calculates the number of occurrences using math
D. It only works with numeric data
Q4. Why did Richard switch the function parameters from String to Variant?
A. To support larger text fields
B. To allow using the function in SQL statements
C. To handle null values without causing errors
D. To make the function compatible with older versions of Access
Q5. When would performance improvements from this "math-based" approach become more noticeable?
A. Only when using single-record queries
B. When dealing with very long strings or large datasets
C. When using simple queries with few records
D. Performance would never improve; it just makes code more elegant
Q6. Why is handling null values important when working with Access queries?
A. Null values always return true for string comparisons
B. Null values can cause query errors if not handled
C. Null values are automatically converted to zeros in all queries
D. Null values only affect date calculations
Q7. Which module type should you use to add the CountOccurs function in Access?
A. Form Module
B. Class Module
C. Standard Module (global module)
D. Data Macro Module
Q8. According to the video, what should you do if you want to count how many times the letter "R" appears in a text field?
A. Use the function with the entire string and the character "R" as the small string
B. Use only built-in Access string functions
C. Create a separate query for every character
D. It is not possible to count single characters with this approach
Q9. What common programming skill does Richard reference as influencing his approach to problem solving?
A. C++ object-oriented design
B. SQL optimization
C. Thinking in loops from BASIC programming
D. Functional programming in Python
Q10. What principle does Richard suggest for optimizing database performance?
A. Always use the latest version of software
B. Offload as much processing as possible to the database/server using efficient solutions instead of loops
C. Never use SQL for data manipulation
D. Stick only with legacy coding practices
Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-B; 7-C; 8-A; 9-C; 10-B
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 video from Access Learning Zone focuses on counting how many times a specific string or character appears within another string using Microsoft Access and VBA. For example, if you have a larger phrase like "Richard is super awesome" and you want to find out how many times "Richard" shows up, you'll get a result of one. If you have a sentence like "Tasha was killed by Black goo. Black goo killed Tasha," and you look for "Tasha," you'll see the answer is two. This method lets you determine the count of any substring or single character within a larger string.
This tip is intended for those comfortable working at the developer level, specifically with VBA. While it does require some basic knowledge of VBA, you do not need to worry if you are new to programming. I walk you through the essentials in the video, which runs about 20 minutes. Newcomers to VBA should be able to follow along and pick up useful skills as they go.
It might feel familiar if you've seen my earlier videos. I originally covered a similar topic a couple of years ago in my For Next Loops video, where I demonstrated a brute force technique for counting string occurrences. That solution worked by using a loop to step through each character in the string and compare it to the target substring. This brute force method has served me well for many years, and there is nothing fundamentally wrong with it.
The approach involves checking each character in the target string and comparing it with your search string. If they match, you increment a counter and then continue through the rest of the string. This method is rooted in classic programming logic, as many of us first learned with older languages like BASIC, where loops were a natural solution for these kinds of tasks.
However, as I progressed as a developer, I discovered a more efficient and elegant way to solve this problem, which relies on manipulating the strings directly rather than looping through them character by character. Instead of looping, you can use a string replacement trick: replace each occurrence of your target substring within the larger string with nothing (an empty string), then measure the difference in length between the original and the modified strings. If you then divide the length difference by the length of the substring you are looking for, you derive the count of occurrences.
Take this example: you want to count how many "R" characters are in "My name is Richard Rost." You could loop through each character, but with this new method, if you remove every "R" from the string and compare the number of characters before and after, the difference tells you how many "R"s there were. The same trick works for longer substrings too. If you want to count how many times "Tasha" appears in "Tasha was killed by Black goo. Black goo killed Tasha," simply remove all instances of "Tasha," check how many characters you removed, then divide by the length of "Tasha" to get the answer—two. This method is not only more elegant, but much faster, especially when working with large data sets.
For those with access to my Code Vault, gold members can easily copy the required function, but everyone else can just type it out as shown in the video.
Once you have the function, you should place it in a global module within your Access database. This way, you can use it throughout your queries, forms, or anywhere else you need this functionality. If you do not already have a global module, create a standard module for this purpose.
You will also notice I have updated the function to use the Variant data type instead of String. The main reason is that if you try to pass a null value to a string-based function, you can end up with undesirable errors showing as Error in your queries. By checking for null values using IsNull, the function simply returns zero when it encounters nulls, offering a more user-friendly output.
Let's put this into practice. Suppose you want to find out how often a customer's first name appears in the notes field of your Customer table. Simply create a new query, use this new function in an expression referencing both the notes and first name fields, and run it. You will instantly see the count of occurrences for each record. You can also use literal values if you want to check how many times a particular character, such as "R" or "!" appears within a field.
This topic is also a good reminder that iterative solutions, like loops, are not always the best choice for performance. While it is often easier to write a brute-force loop when you first begin programming, you may want to revisit such code if you are noticing slowdowns, particularly with large data sets. Optimizing your approach can save significant time and resources. As developers, we should always look for ways to improve and streamline our processes. I still find myself learning new and better techniques all the time, and I encourage you to keep evolving as well.
That covers today's tip on counting substring occurrences, plus a bit of insight into how programming habits evolve. I hope you found this useful and perhaps picked up a new way to optimize your database operations.
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 occurrences of a substring within a string in VBA
Brute force approach using loops to count substrings
Elegant method using Replace and length difference
Calculating substring count with division by substring length
VBA function to count substring occurrences
Handling null and empty string values in VBA function
Using the CountOccurs function in queries
Passing field values and literals to the function in queries
Changing function parameters from String to Variant to handle Nulls
Inserting the function in a global VBA module
Practical example with Access query and Customer table
|
