Word Count
By Richard Rost
4 years ago
Count Number of Words in a Text Field in Access
In this Microsoft Access tutorial, I'm going to show you how to count the number of words in a text field in Microsoft Access. We'll use the Replace function and some other cool VBA tricks.
Members
Members will learn how to deal with people who still type two spaces after a period (for those people who learned how to type in the 70s) and how to deal with hyphenated words like "five-year mission."
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!
Pre-Requisites
Links
Recommended Course
 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, #fasttips, count number of words, Count Words in a Field, Count Words in a Cell, How to View Word Count
Subscribe to Word Count
Get notifications when this page is updated
Intro In this video, I will show you how to count the number of words in a long text field in your Microsoft Access database using a simple VBA solution. We'll go step by step through setting up an unbound text box and button on your form, discuss naming conventions, and use the Replace and Len functions to calculate word counts. We'll also cover handling blank or null fields to ensure accurate results. This tutorial is perfect for anyone looking to add a word count feature for resumes, assignments, or any large text entry in Microsoft Access.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, we're going to talk about word count. I'm going to show you how to count the number of words in a text field—specifically, a long text field, but you could also do this with a short text field if you wanted to—in your Microsoft Access database.
This is a question I get asked every now and then. People ask, "How can I count the number of words in a particular field?" For example, people who copy and paste resumes or homework assignments want to know how many words are in them. Now, Microsoft Word does this automatically, but you can very easily do it in Access with just a wee bit of code.
That's why I put "Developer" up here. Don't be scared if you see developer. This just means it needs a little bit of VBA. I just started doing this, so if you've never done any programming before in VBA, go watch this video. It's about 20 minutes long. It teaches you everything you need to know to get started. VBA is not scary. Don't be afraid of it. It's really easy. I'll walk you through it step by step, but go watch this first so you have some background.
We're also going to use an If Then statement. If there's no text in this field, then the word count is zero— that kind of thing. Go watch my string functions video. Specifically, we want the Len function—how to calculate the length of a string, how many characters are in a string. This will come in handy.
If you don't know what null values are, go watch this. A null value is basically a field that has nothing in it. So, no, there's no value. It's null. Go watch all those videos if you don't know what I was talking about with any of those things, then come on back. I'll wait for you. Go on, go do it now.
Okay, so here I am in my TechHelp free template. This is a free database you can grab a copy of from my website if you want to. Let's say we've got this long text field here. We want to put something in it. Let's go to James Kirk here. I just happen to have some text in my clipboard. Let me paste it in.
Okay, there we go: "Space, the final frontier." You have to say this in either Kirk's voice or Picard's voice in your head. I think, honestly, I love Picard more, but I think Kirk did this better: "Space, the final..." Let's just make a little room here so we can see what's going on.
Let's say we don't need this. Let's make this a little bigger. Let's get rid of these guys—we don't need those. Let's make this bigger, like this—come here, make it nice and big.
Okay, let's put a text box down here that we can put our number of words count in. You can just grab one of these. I'm just going to grab one of these. Let me just grab this one, copy and paste—it's easier to copy and paste something that's already on the form. Slide you over like that. Slide you over like this. We'll put in here "Words."
Now, this guy is already bound to the Country field, so let's go to All, and we're going to get rid of that control source. Now it's not bound anywhere—it's an unbound text box. That means it doesn't get its data from the table; you can put whatever you want in there.
Let's give it a good name. We'll call this "NumWord." I don't like the word "Word," because "Word" is a reserved word. If you don't know what reserved words are, I'll put a link to a video down below you can go watch. Basically, they are words in your database you shouldn't use, like "Name" or "Date."
Okay, so let's make a default value here. Let's go with zero just in case, and we'll put a little button here that we can click on. I'm just copying one of these buttons. Copy and paste, slide down here, put in here "Count." Right click on this button to count the number of words up top, just like that.
Let's give this button a good name—I'm not going to leave it as "Command32." Let's call it "CountWordButton." In fact, that reminds me, I have an informal rule that I use: I try to keep everything singular. I don't always remember, so let's change "NumWords" to "NumWord." Sometimes when you're going along in your coding and you get to a field, you have to type in and you're like, "Oh man, was it NumWord or NumWords?" That messes me up all the time.
I have a field in my website's database called "CommentsT" for comment table, and that messes me up constantly. So since then—fairly recently—I've tried to keep everything singular if I can.
I point that out because it's a tip. It's a tip for me and hopefully it's a tip for you. That's the whole reason you're watching these videos—to get tips.
All right, so let's close this down, close that down. Let's come back into our form here. Let's go back to Jimmy Kirk. There we go. All right, now my button doesn't do anything yet, so let's put some code in the button. When I click on the button, it will count these words up.
How are we going to count these words? There are a couple of different ways you could do it. Since I grew up with BASIC programming, and had my first Tandy Radio Shack Color Computer when I was like eight years old, for me, I always think of programmatic, looping-type solutions. That just comes naturally to me because it's what I learned first as a child.
So, my first instinct is to do a For Next loop—start at the beginning and just count down each character in this string and just count every space. That is certainly fine. If you take the number of spaces in here and add one, you get the number of words. If you only have three words in here, you'll have two spaces—add one and you have the total number of words in here.
We'll get to talking about hyphens later. That's a matter of whether you want to define that as one word or two; we'll talk about that later.
So that's my first thought, and that certainly would work fine, but take a step back and try to think of whether there is an easier way. Brute-force approaches like that tend to not be very efficient, and with something that small, it's not a big deal. But if you've got tens of thousands of words in there, that might take a little while to process.
Think about it mathematically: if I take the total length of that string, and then remove all of the spaces and calculate the length of that, the difference between those two numbers should be the number of spaces in there. Then that is the number of words, of course, add one. If I've only got, let's say, this is my string here—that's what, 16 characters across? If I remove the two spaces and count it again, that's 14 characters—the difference is two, add one, three words. That's how we're going to approach this.
Yes, a lot of times when I'm programming stuff, I'll do the brute-force approach first to get it to work, so I've got a working solution. Then I usually go back and think of other ways I could optimize this to make it faster, to make it more efficient, so I don't have to just loop through everything constantly.
Once I learned Recordsets (for those of you who are a little more advanced and know what Recordsets are), I tend to see Recordsets in my sleep. That's how I want to solve every problem, even though sometimes the standard SQL statements will work just fine too, like an Update statement or an Append statement or whatever.
Okay, so let's see how this code works. Ready? Let's go to Design View, right click, build event, bring up my Code Builder. All right, here's my button.
If you're thinking, you don't have to use a button to do this. You could use an After Update event for the Notes field, so every time it's updated, it will update the count. You can also put it in the On Current event, so as you move from record to record, it will constantly update that count—that's up to you. I'm just going to throw it in the button, but I have videos on how to do both of those things: After Update events and On Current events. I'll put links down below if you want to go watch those.
The first thing we're going to do is check to see if this field is blank or null. If IsNull(Notes)—that's the name of the field—or if Notes is the empty string: now, there's a difference between those. I've got a whole separate video on that coming out. There are null strings and there are empty strings: null values and empty strings. There's a slight difference. Basically, a null value never had a value, and an empty string might have had a value at one point that was erased. There are other subtleties too, but that's the basics of it.
You have to check for both of those situations sometimes, and this is one of them. In this case, we're going to say NumWord = 0; there are no words.
Else, I got some stuff.
All right, so how are we going to calculate this? Well, it's very simple. It's going to be NumWord = the length of Notes minus the length of some other string, plus one character. Now, what's the other string? Well, we're going to take Notes and we're going to replace the spaces with a blank: Replace(Notes, " ", ""). So, the Replace function is going to take whatever is in Notes, find every space, and replace it with an empty string.
In this particular instance, you don't have to worry about it actually modifying Notes, because the Replace function only returns the result; it does not change the underlying data.
Sometimes we'll get—oh, I'm missing a parenthesis there. We have to be careful, right? Replace(Notes, " ", ""). Make sure to close the parentheses. There we go.
All right, Ctrl+Y to get rid of that extra little line in there. Save it. Let's give it a good Debug Compile just to make sure. All right, everything looks good. Come back over here. We're going to close that and open it back up again. Let's do it on this one first. Click—there are four words. Look at that! It counted them all up, took out the spaces, and added them all up again. There were three spaces removed, so the total is four.
"Space, the final frontier"—hit the count button, boom—37. There you go.
Now, it's 37 if you count "five-year" as one word. What if you want to deal with that as two words? We'll talk about that in the Extended Cut. I will also talk about how to deal with anyone who still puts two spaces after a period. If you learned how to type in 1975 and were taught to put two spaces after every period, you're going to get a wrong word count. I'll show you how to deal with that in the Extended Cut as well.
What's the Extended Cut? That's the extra video for the members. Join to become a member—you get all kinds of extra stuff. I just recently started doing Extended Cuts for the Fast Tips, because I got my TechHelp videos too. Those are a lot longer; the Fast Tips are generally shorter. I don't know—basically the same stuff, just longer or shorter, like the pig with the curly or straight tail—your decision.
I'll do a little extra stuff like this. We'll deal with the double spaces. We'll deal with the hyphens. We'll do some looping and we'll do some other crazy stuff.
If you like this kind of programming and you think it's kind of cool—because it is—and you want to be one of the nerdy kids and join us programmers, check out my Access Developer Level 1 class on my website. I'll put a link down below. I'll teach all the cool things you need to know to start being a programmer in Microsoft Access.
That's your Fast Tip for today. I hope you learned something, and I'll see you next time.Quiz Q1. What is the main objective of the video?
A. To learn how to calculate the number of rows in a table
B. To learn how to count the number of words in a text field in Microsoft Access
C. To format text fields with color
D. To import data from Word into Access
Q2. Which Microsoft Access feature does the video focus on to accomplish the task?
A. Macros
B. VBA (Visual Basic for Applications)
C. Queries
D. Relationships
Q3. According to the video, what is the main mathematical approach used to count words in a text field?
A. Counting all punctuation marks
B. Subtracting the length of the string without spaces from the length of the original string, then adding one
C. Counting the number of vowels in the string
D. Using a sum query on the field
Q4. Before writing code, what does the instructor recommend checking in the text field?
A. If it contains numbers
B. If it is empty or null
C. If it is visible on the form
D. If it has formatting applied
Q5. What function is used to remove all spaces from a string in VBA?
A. Left
B. Right
C. Replace
D. Find
Q6. Why does the instructor recommend not naming fields or controls "Word"?
A. It is too generic
B. It is a reserved word in Access
C. It will not display properly
D. It causes formatting issues
Q7. What is an unbound text box?
A. A text box that is read-only
B. A text box not connected to any field in the table
C. A text box that can't be named
D. A text box that auto-formats text
Q8. What solution is suggested for updating the word count automatically when the field changes?
A. Using a macro to refresh the field
B. Using the After Update or On Current event
C. Reopening the database each time
D. Typing the count manually
Q9. In the code example, what value is assigned when the field is blank or null?
A. One
B. -1
C. Zero
D. The maximum record count
Q10. What extra challenges are mentioned that can affect accurate word count?
A. Special characters and numbers
B. Font size and formatting
C. Double spaces after periods and hyphens
D. Table relationships
Answers: 1-B; 2-B; 3-B; 4-B; 5-C; 6-B; 7-B; 8-B; 9-C; 10-C
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 how to count the number of words in a text field in Microsoft Access. This method works especially well with long text fields, but you could use it for short text fields too if you prefer.
People email me every so often asking how to count the words in a particular field. For instance, it's useful for anyone pasting in resumes or homework assignments and wanting to check the word count. While Microsoft Word calculates word counts automatically, you can achieve the same result in Access with just a little bit of programming.
I want to reassure you that we will use a small amount of VBA code. If you're completely new to VBA, I have an introductory video that covers the basics of getting started with VBA in about 20 minutes. I suggest watching that video first if you don't have any experience with VBA yet. Trust me, it's not difficult at all and I'll guide you through the process step by step.
This word count solution uses some simple logic, including an If Then statement. If there's no text in the field, the word count should show zero. If you need a refresher on string functions, especially the Len function which tells you how many characters are in a string, I encourage you to check out my string functions video. It's also important to understand null values in Access; a null means there's nothing stored at all in a field, which is different from just an empty string. If this distinction is new to you, I have a separate video explaining null values that you should look at first.
Moving forward, let me describe what I did in my TechHelp free template database. This is a sample database you can download from my website if you'd like to follow along. I started by working with a long text field and pasted in some sample text from Star Trek—specifically "Space, the final frontier." You can imagine hearing it in either Kirk's or Picard's voice.
To make this work, I cleared out form elements I didn't need and resized the form so that the relevant fields were easy to see. Next, I added a text box to the form to display the word count. I copied an existing text box, pasted it where I wanted it, and removed its control source so it became an unbound text box—that means it doesn't get its data directly from the table, but will instead display calculated data.
I gave this text box a proper name, deciding on "NumWord" rather than "NumWords." I try to keep control names singular, since consistency helps avoid confusion later in code. Using reserved words like "Word" can occasionally cause problems, as they may conflict with database keywords, so if you need more detail on reserved words, I also have a video explaining what those are.
For the starting value in the word count text box, I set the default to zero. Then, I added a button labeled "Count" to the form and named it "CountWordButton," again sticking to the singular. I recommend always giving your controls meaningful names rather than using the automatically generated ones from Access.
From there, I closed and reopened the form to reset everything and ensure my changes were saved. At this point, the button didn't do anything yet, but I was ready to add the code to make it work.
Now, let's discuss how to count words efficiently. My original background was in BASIC, so my first instinct was always to loop through the string character by character and count spaces. With this approach, if you take the number of spaces and add one, you get the number of words. While this works, it's not necessarily the most efficient method, especially if you're working with a large amount of text. Brute-force techniques like looping are fine for short strings, but efficiency matters as your data grows.
Instead, I thought about a more mathematical approach. Rather than checking every character, consider this: if you calculate the total length of the text string, and then calculate the length of the same text with all spaces removed, the difference between those two lengths will give you the number of spaces in the text. Add one, and you have the total number of words. For example, if your string is sixteen characters long and, after removing two spaces, it's fourteen characters, then you know there are two spaces and thus three words.
This approach is efficient and does not rely on looping. When developing solutions, I often start with a method that gets the job done, then look for ways to optimize it.
When adding the code to the button, I remind my students that there are other ways to incorporate this logic into your form. You could, for example, include it in the After Update event for the text field itself, so the count updates automatically when users edit the field. Or you could place it in the form's On Current event, so the word count updates as you move from record to record. In today's example, though, I placed the code behind a button to demonstrate the basic approach. If you want to learn about those other event handling methods, I have additional videos covering them.
The basic process in the code is this: first, check if the text field is blank or null. In Access, a field can be null (meaning it never had any value), or it can be an empty string (it had a value that was deleted). For accuracy, it's important to check for both. If the field is either blank or null, the word count is zero.
If the field has content, we calculate the difference between the length of the original string and the length after removing all the spaces, then add one for the number of words. The Replace function facilitates this part by substituting every space in the text with nothing, and then you just compare the original and the new lengths.
After entering and saving the code, I tested it with various entries. For example, the phrase "Space, the final frontier" is correctly counted as four words by this method.
There are, however, some nuances to consider. Suppose you have hyphenated words like "five-year." Do you want to count that as one word or two? If you or your users are in the habit of typing two spaces after a period, which was common if you learned to type long ago, this technique will miscount the words. Handling these issues requires extra steps.
In today's Extended Cut video, I go into detail on dealing with these edge cases. I'll show you how to correctly handle hyphenated words, double spaces, and other special situations. Extended Cuts are additional tutorials for site members, where I explore more advanced or specialized scenarios. If you're interested in deeper topics like these, you might want to check out my membership options.
For those who are interested in learning more about Access programming and want to become more adept with VBA, I recommend my Access Developer Level 1 course. You'll find a link for that on my website as well.
That covers our Fast Tip for today. For a complete video tutorial with step-by-step instructions showing exactly how to implement everything I've discussed, visit my website at the link below.
Live long and prosper, my friends.Topic List Counting words in a long text field in Access
Creating an unbound text box for word count display
Naming controls to avoid reserved word conflicts
Setting a default value for controls
Adding a command button to run word count
Writing VBA code to count words in a text field
Using the Replace function to remove spaces
Calculating word count by comparing string lengths
Handling blank and null fields in VBA
Assigning code to form controls in Design View
Checking for empty string vs null values in Access
Testing the solution on sample data in a form |
