Count Characters
By Richard Rost
6 years ago
Count Specific Characters in a String with a For Next Loop
In this Microsoft Access tutorial, I will teach you how to use a FOR NEXT statement in Microsoft Access VBA to count the number of time a specific character occurs in a string. We can use this to also count how many lines are in a long text field (textbox).
Paul from Houston TX (Silver Member) asks, "I get emailed a (text) list of products every day. Next to each product is a + if that product is new. I need to know both of these counts to put in my daily report. Is there a way I can just copy and paste this text into Access and have it count the number of lines and the number of + it finds?"
Members
I'll show you how to create your own function. We will make CountChar into a function that returns a value. We will learn about Rich Text Fields and how to add color and HTML codes to your long text fields. We will learn how to count the number of open ( and close ) in a box and change the colors accordingly to allow you to see where they may not match up.
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!
 Update
While the CountChar function that we create in this lesson works just fine and is a great teaching example, I have since discovered a faster, more efficient method for calculating these occurrences. See the updated, new and improved Count Occurrences video.
Links
Concatenation: https://599cd.com/concatenation
Enter Key Behavior: https://599cd.com/enter+key+behavior
New Line in Field: https://599cd.com/new+line+in+field
For Loops: https://599cd.com/acd3
Intro to VBA: https://599cd.com/IntroVBA
LEN Function: https://599cd.com/acd8
MID Function: https://599cd.com/acx25
While Loop: https://599cd.com/While
Create Functions: https://599cd.com/acd2
Create Public Module: https://599cd.com/acd11
Exit Function: https://599cd.com/exit+function
DoEvents: https://599cd.com/acd12
Rich Text Boxes: https://599cd.com/acx5
Arrays: https://599cd.com/arrays
Combo Box .Column: https://599cd.com/acd15
Extras
Alex's Online Balance Braces Tool
Intro In this video, I will show you how to use For Next loops in Microsoft Access VBA to count the occurrences of specific characters, such as the plus sign, within a pasted text string. We will set up a simple Access form to allow for pasting product data, and I will demonstrate how to count both the total number of characters and the number of lines in the text, using string functions like Len and Mid, as well as handling new line characters. This tutorial is great for anyone who wants to automate counting tasks in their Access databases.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's lesson, I'm going to show you how to do For Next loops in Access VBA, and how to count the occurrences of a specific character inside a string.
Today's question comes from Paul in Houston, Texas, a Silver member. Paul asks, I get emailed a text list of products every day. Most of each product is a plus sign if that product is new. I need to know both of these counts to put in my daily report. Is there a way I can just copy and paste this text into Access and have it count the number of lines and the number of plus signs it finds?
Paul sent me an email copy of what he gets from his distributor. He doesn't really do anything with that data except he needs the count of the total number of products on that list and how many of them are new.
So what we're going to do is we're going to learn a For Next loop so we can loop through that whole string of characters, and we're going to count the number of plus signs, and we're going to count the number of new line characters. At the end of each of those lines, there's something called a new line character, and I'll show you what that is in just a minute.
Here I've got a blank Access database. Let's create a form. Create, Form Design. Here's a big open blank form. I'm going to turn on my ribbons here, and let's come down. I'm going to make this a little bit small. It wouldn't have to be quite that big. Let's resize this guy.
Now, this form is not going to have any data in it. We're just going to let Paul paste in here the text that he gets. So let's give it a splash of color. I always have to put some kind of color behind my forms. Let's go with blue like that, and we'll want a big text box in here. So let's go to Design and grab a text box and drop it right there like that. Just big enough to paste this stuff, and I'm going to delete that label. We'll just save this as my paste form. What are we going to call it? Paste F. All right, let's see what we got.
And yeah, we can turn off the navigation bars and all that stuff. Let's do it because it's going to annoy me if I have to look at them. Let's go into the form property. Some of you haven't seen this. I know, form properties. We're going to turn off scroll bars because we don't need them. We're going to turn off... I'm on the format tab, by the way. We're going to turn off the... let's see what we got here... the navigation buttons because there are no records, and the record selectors. That makes this form look a little more like something that doesn't have any data in it. See?
I've got Paul's data right here. Now, this looks easy to do by eye. I can just very easily see here, there's seven records and there's three pluses. But the record list that he sent me was 4,000 items long. You could paste this into Excel and figure out how many rows there are and there are some other tricks you could do to count the number of pluses. But he is using Access already. He wants to just take this, copy it, paste it into his database, and then hit a button and just get his counts. He is actually saving this information in his database too. So we'll kill two birds with one stone.
The key here also is it gives me an opportunity to teach you how to do For Next loops because that's something you should learn how to do. I like to make Access do my work for me.
I've got another video I'm going to do soon that I get emailed personally. I get emailed information from my bank every day, and it's got balances and stuff on there. I'm going to show you how to copy all that information and paste it into your database and pull out the bits of information that you need, like opening balance, closing balances, like that. That will be a future video, and you'll need this stuff to do that stuff. So we're going to learn For Next loops first.
Let's go into Design View. Let's drop a command button on here. So go up to your Design ribbon and find the command button and drop it right there. The wizard will start up; cancel it. There is no wizard for this. Here's a caption to go or count, or whatever you want to call it. Right click, Build Event.
Now a little window might pop up. If you're new to this, it says what kind of builder do you want. You're going to pick the Code Builder. Now we're inside of Command 2. Click, that is command button 2. It's actually the second object on the form. We should give it a good name. Alex is going to yell at me if we don't. Close that. Come back in here.
If you don't know who Alex is, you haven't been watching my videos long enough. Open up this button. Let's change the name of this to my count button. Okay, count button.
Now, see these little tips and tricks I try to put in the TechHelp videos. Right click, Build Event. That puts us back in here. Now I'm in Private Sub count button. Click. Are you happy, Alex?
So before we count the number of characters in that box, let's first learn how a For Next loop works. First you Dim a variable called x or whatever you want to call it. I'm going to make this a Long Integer. That says give me a bit of information, we're going to call it x, we're going to save it in the computer's memory, and it's a Long Integer. X can be whatever I want. It's not a field in a form, it's not a field in the table, it's just some memory variable. It's some bit of computer memory, it's going to hold some information. Take my Access Developer 1 class if you want to learn all the nitty gritty and the tips and tricks of Visual Basic stuff.
But I'm going to teach you a little bit here and there on these TechHelp videos. If you want the comprehensive course from A to Z, take my Developer courses. I know lots of you from the emails that I get like to learn little bits of information here and there, and that's what these TechHelp videos are perfect for if you don't want to take a full long course. And that's fine.
Now let's just make this value x count from one to five and then do something. How about throw up a message? So I'm going to say right here, For x = 1 To 5. At the bottom, I'm going to put Next. In some older programming languages, you have to put Next x because you can have loops inside of loops, as we call nested For loops, and the Visual Basic editor needs to know which loop you're ending.
But VBA (Visual Basic for Applications), which is what comes with Access and Word and stuff, you don't need to put that variable name there. In fact, I'm pretty sure it gives you an error message if you do. Let's see... now I'll let you. But you don't have to. It automatically assumes if you have nested ones that the first Next is for the innermost loop and so on. Having one loop exit before the other one does is not considered proper coding.
So what are we going to do for this one to five? We're going to say MessageBox x. That's all. Just pop a message on the screen and show me what that number is.
Save this. Come back out here. I'm going to close my form and reopen it. I always have to get in the habit of closing these and reopening them. Let's hit the count button. One, two, three, four, five. That's all that code does, and now you know how to program a For Next loop.
Now, how about instead of message boxing, how about we put that value inside this box here? Let me shrink this down. I'm going to resize Access like this, and I'm going to resize my code window so we can see them both at the same time in the same video window. Because this code is not going to be giant big in this form. It's not going to be much bigger than that. So this should work well.
So instead of popping this value up on the screen, let's put this value inside this box. First we need to know what the name of this box is. Let's see, Design View. Let's open up the properties for this box. Right, that's Text0. I don't only Text0. Let's just call it, I don't know, Box1. Let's call it Box1. Close that.
Now I'm going to come over here and say Box1 = x, just like that. Save it. Let's close the box and reopen it and hit the button. Boom. And there's just a five in there. Why is that? Well, what we did was we looped from one to five. We set Box1 equal to one, then Box1 equal to two, then Box1 equal to three. We basically replaced the value in Box1 each time we looped.
If you want this to say one, two, three, four, five in it, you have to add it to the value of what's in that box. So first we'll start off up here and we'll say Box1 = "", that's an empty string. Now in here we'll say Box1 = Box1 & x. That's called string concatenation. I've got a whole separate video on how that works. If you don't know how adding strings together works, watch my video on string concatenation; I'll put a link in the description below the video.
Let's hit the button. Look at that, one two three four five. We've added to the value of the box each time. Now knowing this, now we can reverse this and count the number of characters that are in this box. Instead of adding stuff to it, we can count what's in it, pretty much the same way with the loop. We can loop through each of the characters in here.
Let's drop some text boxes down here so we can see some values. Three variables are nice for little things like these counters. In fact, you can make these text boxes too instead of making the memory variables.
Let's put some text boxes on here. One of the reasons I don't like shrinking this sometimes is because you lose your menus. That's okay.
Drop a text box down here. Let's call this one Total Characters. Let's go black, easy to see. The total characters up here.
Now how do we get that? Well, I just accidentally clicked there. That created a break point. I don't want to do that. I cover that in a different class. That was an oops.
First thing we're going to do is get the total number of characters. Now, this isn't a memory variable. It's down here. It's a field. It's in this guy. So let's name it TotalCare. That's the name of that text box. This guy here is called TotalCare. So I don't need to Dim it. It's not a memory variable. I'm going to say TotalCare = Len(Box1). Let's put this after the loop right down here just for now.
So we'll run this same loop here and then we'll count up how many total characters there are. This is a function, Len, that already basically does that loop. It counts the number of characters in there. I should get a five. Let's check it and see. Let's save this and reopen it and hit the button. Boom. It looped one to five and then TotalCare counted the number of records, or excuse me, the number of characters in that box.
Doing databases, I'm so used to saying records, the number of records on a table or a query or a form. Now let's say I've got data up there like the set of data I was emailed. Now I want to count in here. Let's do the easy one first. Let's count the number of pluses that are in there.
Let's change our code a little bit. TotalCare, we'll determine that first. I'm going to move that up here. Now I know the total amount of characters in this box up here.
I no longer want to set the value of Box1. The user's pasting stuff in here for me, and I'm going to loop from one to TotalCare. So loop from one, the first character all the way to the last character in that box.
Now we're going to change what happens in this loop. Inside this loop, I want to count up the number of pluses. So let's add another text box down here. Copy, paste. Pluses. Let's just call it PlusFound. Make it easier. I like to get in the habit, I started doing this recently, I like to get in the habit of making all my variables and stuff and my field names singular. So instead of Pluses, this will be Plus. That's why the Total Characters, I just put TotalCare.
So for pluses found, I'm just going to call the name of this PlusFound, like that. Over here, I'm going to add up those pluses. It's always a good idea to initialize your values, initialize your variables or your fields. So I'm going to say PlusFound = 0. As of the time this loop starts, give it a starting value. Because if you don't and you run this more than once, this guy's already got a value in it, so each time you run this, we want to make sure this loop starts at zero.
Right inside of here, let me close this so you can see it, it's a little easier if you can see the values. Let me copy... I've got the stuff sitting over here in Notepad, let me copy that and drop it back in there.
Now in here, I'm going to check to see what the current character that I'm on is. How do we do that? We need another variable for that. Let's call it C. Dim C As String. What is C going to be? C is going to be the specific character that I'm on. How do we determine that? Here's another function for you. It's called the Mid function, and I cover... there are a bunch of string functions: there's Left, there's Right, there's Mid, there's a bunch of other ones. I cover those in my classes. Again, I've got some free videos on YouTube about how to do a lot of this stuff. I'll put links below this video so you can go watch those lessons too if you want to learn how these string videos work.
But watch this now: C = Mid(Box1, x, 1). Let me explain what that is. If you look at the IntelliSense that pops up, it tells you: Mid says I want a character in the middle of a string. There's Left, there's Right, which says give me the left three characters, the right one character, whatever. Mid goes right in the middle of the string, to any position.
What is the string? Well, it's Box1, that's what I'm looking inside of. The Start is the count, where do you want to look at it? Well, I want to look at the current position because my x is looping through the entire string; x is one of the total characters. So the Start is start looking right at x. Then the Length is how many characters do you want to grab? Just one. Just the current character. So first it goes character one, character two, character three, and so on. So that's being set in a variable called C.
Now I can just check and see what C is. If C = "+", which is inside quotes, that's a string, then increment our PlusFound counter. PlusFound = PlusFound + 1. And that's it. That's how we count the number of pluses.
So we got the length of the box in characters. PlusFound set our initial value to zero. Loop from x = 1 to the total number of characters, so start here, go one, two, three, four, five, all the way down. There is a character at the end of this called a new line character, we'll talk about that in a second. So that's actually two characters. That's a little more advanced, I'll go into that in a minute. Figure these extra characters at the end here. Then go C = whatever position we're in, one character, that's what C is now. So C will start off first, it'll be an R, then the next iteration of the loop, it'll be another R, then it'll be a 7, and so on. So each time that happens, I'm going to check and see if it's a plus. When we hit a plus, increment that counter.
Let's see if it works. Boom, go. Look at that. TotalCare is 77. There are 77 total characters here. PlusFound, three of them. One, two, three. We just solved the first problem that we had for Paul. I forgot who this was: Paul. Sorry, Paul.
Now I'll tackle the second part, which is actually a little more difficult. Let me change up the data set in here real quick. We're going to do xx and then hit count. Two characters and no pluses were found. If I go xx+, there's three characters.
The default behavior for a text box here is if I hit Enter, it goes to the next field. That's the default. But when you want to get an actual new line in here, you have to hit Ctrl+Enter in a regular text box. When you copy in stuff from Notepad, it's already got these new line characters in it, but Ctrl+Enter. So if I go in here and go like this: Ctrl+Enter, that puts me on the next line.
There's also a property you can change in the text box. Long text fields have this property already set for them. If you go to the Other tab, there's Enter Key Behavior. It's either Default or New Line. For here, I'm going to go New Line. So when I hit Enter, it gives me a new line in the field here. Otherwise, if you've got short text like First Name, Last Name, and so on, hit Enter, you want to move to the next field. For memo fields, the default is Memo (Long Text), same thing. I'm still in the habit of calling them memo. These were called memo fields years ago.
So New Line In Field is the option here. If I come in here and I type in xx, enter, xx, like that, you can see that it moved me down to the next line. Now, if I hit count, now there are six characters there. Why is there six? Because a new line character is actually two characters. There's a line feed and a carriage return. This goes way, way back, probably to the 80s and before.
Now, to check for these carriage return/new line thingies, you have to look for a Chr(10) and a Chr(13). What that means, that's a whole other class. But all we have to do is update our code in here and look for a specific character code. Character code 10 and character code 13. Either one of them will work just fine, because it's a pair. They're always both together. This is a little more advanced. I warned you, we're going to get into some pretty cool stuff today.
So we've got a value for the number of pluses. Let's add another one out here so we can count the number of total lines. Copy, paste. Let's call this TotalLine. The label can say Lines. Actually, let's call it LineCount. Try to keep it singular. Let's call this LineCount.
So same as before. We'll initialize it. LineCount = 0. I'm not going to capitalize it when I'm typing in my VB code because as soon as I move off the line, look at it, it capitalizes itself. I've caught so many mistypings that way. I'll let VB do the capitalization for me.
Now, in here, it's the same thing, but we're going to say if C = Chr(13), that's the new line, or the character... I don't remember which. There's Chr(10) and Chr(13). Those are ASCII character codes. That's a whole different lesson. I'm not going to talk about those right now. Basically, it's code 13. Each letter has its own code. A is 65, B is 66, and so on. So Chr(13) is a carriage return or line feed, one of those two. It doesn't matter, that's irrelevant to the video.
So if we have Chr(13), then my LineCount = LineCount + 1. That gives us the total number of lines. Minus one, that's a different story, we'll get to that in a second too. There are a lot of little If Thens in this lesson.
Save this. Let's close this whole form and reopen it. Hit the button. Oh, Invalid use of Null. What happened here? Debug. For x = 1 To TotalCare. Why? TotalCare is null. There's nothing in the box. We didn't do any error handling or any checking. If IsNull(TotalCare) or TotalCare is empty, because the user can blank it too, then MessageBox. Actually, I'm going to say, yeah, that's just MessageBox: "Box is empty". And then Exit Sub. Get out of dodge.
Hit the button. Now the box is empty, so we just handle that, we just handled it.
Now let's put something in here and see if it works. xx, Enter, xx, Enter, count the lines. Oh, box is empty. What happened? I goofed and I'm going to leave this in the video because I make mistakes too. A lot of the time, it's when I'm not paying attention. I want you to see what I did. Does anyone see the error? Why does it think that the box is empty? Go ahead and take a second, pause the video if you have to, find the error. I like to leave errors in my videos when I goof up because I wasn't paying attention. I was busy trying to be cool and talking to you guys.
A lot of times, it's like when you can do something like swish a basket, and you can do it when you're by yourself a million times, but then as soon as someone's around, you're trying to show off or demonstrate something to someone else, you can't do it, you goof up. I do that same thing. I program this stuff all day long, but when I'm talking to you, I'm a little distracted. I'm trying to be entertaining. I miss stuff. I like to leave these errors in my videos.
Anybody see it? I'm looking at TotalCare, which TotalCare is the count of the box. What I need up here is Box1. There's my goof. Took me half of a second to see it, once I got the error message. You're not perfect, no one is, and you're not going to be able to have perfect code. So knowing how to find that stuff is important, and that's why I like to leave errors in the videos. Trust me, if I took all the errors out of all my videos, they'd be very, very short.
Hit the button. TotalCare is six because we got those two characters in there, PlusFound, and then LineCount is one. Why is LineCount only one? I see two lines there. Well, it's a behavior of Access and how it handles text boxes. If I put extra lines at the end here, and I hit the button, Access automatically trims them. I can hit Enter, Enter, Enter, Enter, Enter six times, I'm down here now. If I hit Count, as soon as I leave this box, Access trims it. That's just a behavior of how Access is. If I go Enter, space, space, and hit Count, Access trims it. It trims any, they're called white space characters: spaces, the new line characters, that Chr(13) and 10, anything that's at the end there, it just chops it off. It thinks it's doing you a favor.
We can take that into consideration by just adding to our LineCount at the very end here. So we can just say LineCount = LineCount + 1. We're going to take that behavior into consideration. Now there are two lines. If you don't type anything in that's multi-line, if I just put some stuff in like this and hit LineCount, we want to see a one there.
Now we've done everything that Paul needs. Hit Count. Let's verify it. Seven and three pluses. One, two, three, four, five, six, seven. And three pluses. Perfect.
Now, if I wasn't taking my time goofing around, we could have probably written this code in about five minutes, but since I was goofing around and showing you people how to program, it took us well, at about 20 minutes right now.
So that is how you do some really cool stuff in VB. And this is just one example. I use For loops all the time. This is one of the most popular loops in Visual Basic. I actually use While loops more because While loops, you don't have to know what you're looping from and to. You can loop from one to whatever, and then inside the loop itself, you can figure out when to stop. That's a whole separate video. I cover While loops in my full classes, of course.
If you want to learn how to program, I've got a whole series of Developer courses for Visual Basic and for Microsoft Access.
I kind of went overboard for the extended cut. It's about another 50 minutes for the video. I went kind of crazy today. I just kept finding more cool stuff to do.
First, we made a CountCare function. I took that little block of code that we used to count the characters inside of a string, and we made a full function out of it. And not only can you just search for a single character, but you can search for a block of characters, and how many times it appears. That's kind of cool. And then I'm like, why don't we build something so we can match up parentheses in a bit of code? I see this happen all the time. I get it where I've got mismatched parentheses or square brackets for your SQL or anything. For you math people, you might have to do curly braces or C programming. So I figured this would be a good opportunity. I teach arrays in that lesson and rich text fields and how to put color in your text boxes using HTML code. And it's a neat lesson.
So if you're interested in learning more and getting into some more programming, it's for my Members Only extended cut.
How do you become a member? All you gotta do is click on that Join button right there. You'll get access to all of my extended cut TechHelp videos. There's a lot of them up there right now, so far 12 or 13. I've been doing this for only about two or three weeks. If you click the Join button, you'll see all the different levels that are available. But don't worry, I'm still going to keep making the free TechHelp videos. They're going to be around forever. I love teaching you guys stuff for free. But make sure you subscribe to my channel, hit the little bell. That way, you'll get email notifications whenever I release something new.
Stop by my website, visit the Access forum, and make sure you click on the little Show More link down below the video, down where you see it says PC Learning Zone Computer Training. Go down below that, you'll see Show More. Click on that and it always opens up. I put lots more links in there for all the different things that I talked about. Like in today's class, we talked about concatenation, we talked about For loops, we talked about string functions like Len and Mid. I've got links down below this video for all those different lessons. Some of them are free on YouTube, some of them are on my website.
YouTube does their best to kind of hide it. That Show More link isn't very visible at all. They should at least make it like blue and underlined or something. And whenever you're looking for something, you can always go to my website and search on the topic index because people always ask me, well, I didn't know if you covered this. Well, there's how you get to the Access index, it's on a drop down box.
Now, if you haven't yet watched my Access Level 1 Beginner class, do that. It's three hours of really good material covering all the basics of Microsoft Access, especially if you're new to it. If you like Level 1, Level 2 is just a dollar. If you become a member of my channel, all membership levels get Level 2 for free. If you want to go beyond that - Level 3, 4, 5, and so on - you can get a discount on those as well if you're a member.
Want to see your question answered in one of these videos? There's the page, TechHelp. We've also got a directory to all of my TechHelp videos that I put online there as well. There's also a playlist on my YouTube channel, but it's easier to find on my website. You can also email me, there's my email address, but I prefer you use the TechHelp page. I get lots and lots of emails, so it's better if you drop it on the TechHelp page.
There's all my other stuff - Facebook, Twitter - but I'm mostly active on my website and on YouTube.
Thanks for watching. I hope you learned something and we'll see you next time. Keep learning.Quiz Q1. What is the main purpose of Paul's request in this video?
A. To have Access automatically count the total number of products and plus signs in a pasted text list
B. To analyze and modify product prices from a daily email
C. To generate product descriptions from email data
D. To update inventory quantities directly from emails
Q2. Why is a For Next loop used in this lesson?
A. To loop through each character in the pasted string
B. To add records to a new table
C. To import multiple files at once
D. To refresh all forms displayed in Access
Q3. What Visual Basic feature is discussed for repeatedly executing code a set number of times?
A. For Next loop
B. Do While loop
C. Select Case statement
D. On Error Resume Next
Q4. How does the video suggest checking the total number of characters in a string?
A. By using the Len function
B. By using the Max function
C. By using the GetLength property
D. By subtracting positions of two letters
Q5. What function is introduced to retrieve a single character from a specific position in a string?
A. Mid function
B. Left function
C. Replace function
D. Right function
Q6. Which text box property allows users to hit Enter and create a new line within the field?
A. Enter Key Behavior set to New Line
B. Control Source set to default
C. Text Align set to left
D. Locked property set to Yes
Q7. What is checked to count the number of new lines (lines) in the pasted text data?
A. Count of Chr(13) (carriage return) characters in the string
B. Count of letter 'N'
C. Number of spaces between words
D. Presence of consecutive plus signs
Q8. What initialization step is important before running the loop to count pluses or lines?
A. Set counters (like PlusFound or LineCount) to zero each time
B. Change text box background color to blue
C. Rename all variables to initials
D. Turn on navigation buttons
Q9. In Access VBA, what will happen if you paste the same data multiple times and do not reset your counter variables before a new count?
A. The count may be cumulative and incorrect
B. The system will crash
C. The original data will be lost
D. The program will ignore new input
Q10. What key consideration does the instructor mention about errors and debugging in programming?
A. It is normal to make mistakes and important to know how to debug them
B. Errors can be ignored in all cases
C. Errors mean your code should never be used
D. Debugging is unnecessary in Access VBA
Q11. When looping through each character in a string to compare if it's a plus sign, which VBA statement is used?
A. If C = "+" Then PlusFound = PlusFound + 1
B. If C = "*" Then PlusFound = PlusFound + 1
C. If C = "X" Then PlusFound = PlusFound + 1
D. If PlusFound = "+" Then C = C + 1
Q12. Why does Access sometimes show fewer lines than expected when counting with new lines at the end?
A. Access trims white space and new line characters from the end of the field
B. Access cannot recognize plus signs at all
C. All blank lines are automatically converted to zeros
D. Entered new lines double the line count
Q13. What lesson does the instructor suggest watching if you want to better understand how to add strings together?
A. String concatenation
B. Sorting algorithms
C. Query design
D. Data import wizard
Q14. What basic error-handling method is added to the routine if the box is empty before running a count?
A. Check for null or empty and exit the code with a message box
B. Automatically restart Access
C. Replace all contents with zeros
D. Delete the entire form
Q15. What additional feature does the instructor discuss in the extended cut of the video?
A. Creating a function to search for a block of characters, use arrays, and color text using HTML code
B. Automating daily email downloads
C. Printing barcodes for each product
D. Importing data from Excel
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-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 video from Access Learning Zone covers how to use For Next loops in Access VBA and how to count the occurrences of a specific character within a string. I received a question from Paul, who gets a daily email with a list of products. In his data, new products are marked by a plus sign. Paul wants to paste this text into Access and get a count of both the total number of products and the number that are marked as new.
For this task, we need to create a form in Access where Paul can paste the product list. The form does not need to store records. Its sole purpose is to provide an interface for inputting the data and running the count. I like to visually distinguish my forms with some color, so I gave it a blue background and added a large text box for the pasted list.
After setting up the form, I removed elements like navigation bars and scrollbars, making it look cleaner and focused just on the data input. With the setup complete, I explained that while by eye it is easy to count a small list, the actual list Paul receives may be thousands of lines long. He could use Excel, but since he is already using Access, we can automate this task directly in his database and also save the results for future use.
The heart of this lesson is learning the For Next loop. In Access VBA, a For Next loop lets you iterate through a string or any set of values. We will use this loop to go through all the characters in the pasted list, identifying plus signs and line breaks.
To start programming, I added a command button to the form and set up its event handler in the code editor. I also gave the controls and variables meaningful names. That way it's clearer what everything is used for. Naming conventions are important, and they help keep your projects organized.
The For Next loop works by declaring a variable, commonly x, which acts as a counter. This variable is not tied to any table or form field; it lives in memory just for the loop. From here, you tell VBA to repeat a block of code for each value from a starting point to an end point. For demonstration, I first set up a simple loop from 1 to 5 and displayed the counter value in a message box. This was just to show the structure of a For Next loop.
Moving on, instead of showing the count in a message box, I updated the value in the form's text box directly from the loop. The key point here is realizing that if you overwrite the value each time, only the final value is visible. To build up a string of all the values, I showed how to concatenate values, adding each new number to the end of the box content.
Next, I introduced a way to count the total number of characters in the text box by using the Len function, which provides the length of a string. We displayed this result in another text box on the form.
To focus on counting plus signs, I explained that you want to loop through the characters in the text box and check if each is a plus sign. For each plus found, you increase a counter. Before starting the loop, you should always initialize your counter to zero to ensure accurate results every time the code runs.
To get the current character at each position, I used the Mid function, which extracts a specific character from a string based on its position. So, for each iteration of the loop, we pull out the character, test if it is a plus, and if so, increment our plus counter.
With this method, we accurately count the number of plus signs in the string, regardless of how large it is. This solves the first part of Paul's problem.
Next, I addressed counting the number of lines in the pasted text. The challenge here is that a new line in Windows is usually two characters: a carriage return and a line feed. When working with multiline text boxes in Access, these are represented by ASCII characters 10 and 13. I set the text box to allow new lines so that pressing Enter inserted the correct characters.
To count the lines, I added another text box for the result and, in the loop, tested for the presence of a carriage return character (Chr(13)). Each time one appeared, the line counter was incremented. I also explained that Access trims any trailing new lines or spaces when you leave a text box, so the last line needs to be accounted for by adding one to the final count after looping through the string.
During coding, I intentionally left in an error to demonstrate basic debugging skills—always a valuable lesson. When trying to count characters or lines, one must ensure the correct variables are checked and that empty or null situations are handled gracefully to avoid runtime errors.
After refining the program, we successfully counted both the number of plus signs and the number of product lines in the pasted text, producing exactly what Paul needed, even for lists containing thousands of products.
Towards the end, I mentioned that I often use While loops as well, but For Next loops are ideal when you know exactly how many times to iterate. For those interested, my full Access Developer courses go into greater depth about loops and many other VBA programming concepts.
For those wanting more advanced lessons, today's Extended Cut goes even further. We build a CountCare function that can count not just single characters but strings of any length within another string. I also introduce a way to use these techniques for checking matched parentheses, which is helpful when working with code or complex data formats. Along the way, I introduce arrays, working with rich text fields, and even how to add colored text to your forms.
Extended Cuts and additional techniques are available for members of my site and channel. To view these extended lessons, you can join as a member and access a wide range of additional content. Rest assured, the main TechHelp videos remain free and available for everyone. Make sure to subscribe to my channel to stay informed about new uploads.
Finally, I encourage everyone to visit my website for additional resources, video indexes, and free tutorials on topics covered in this lesson such as concatenation, string functions like Len and Mid, and many more. If you are new to Access, you should check out my Access Level 1 Beginner class. And if you want to ask questions or see them answered in a future video, use the TechHelp page on the website.
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 Creating an Access form for user data input
Customizing form properties for interface clarity
Adding and configuring a multiline text box
Adding and renaming command buttons for actions
Writing VBA code for button click events
Declaring and using variables in VBA
Explaining and implementing a For Next loop
Displaying loop values using MsgBox
Demonstrating string concatenation in VBA
Accessing and modifying text box values via VBA
Calculating total characters using the Len function
Looping through each character in a string
Using the Mid function to read string characters
Counting specific characters (plus signs) in a string
Initializing and updating counters in VBA
Adding text boxes to display result counts
Handling new line and carriage return characters
Counting the number of lines in pasted text
Identifying and working with Chr(10) and Chr(13)
Handling Access text box Enter key behavior
Implementing basic error handling for empty inputs
Adjusting code to account for Access trimming behavior
Incrementing line count to reflect actual rows
Testing the solution with sample data |
