How to Count Occurrences of a Character in a String with a For Next Loop in Microsoft Access VBA
HTML-код
- Опубликовано: 14 окт 2024
- 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?"
LEARN MORE:
599cd.com/ForNext
LINKS:
Concatenation: 599cd.com/conc...
Enter Key Behavior: 599cd.com/ente...
New Line in Field: 599cd.com/new+...
For Loops: 599cd.com/acd3
Intro to VBA: 599cd.com/acd1
LEN Function: 599cd.com/acd8
MID Function: 599cd.com/acx25
While Loops: 599cd.com/acd12
BONUS FOR CHANNEL MEMBERS:
Silver Members and up get access to an EXTENDED CUT of this video which covers additional examples. That video will show you how how 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.
MEMBERS ONLY VIDEO:
• For Next Loops in Micr...
BECOME A MEMBER:
/ @599cd
MEMBERS TOPICS:
Make a CountChar Function
Create Functions: 599cd.com/acd2
Create Public Module: 599cd.com/acd11
Exit Function: 599cd.com/exit...
DoEvents: 599cd.com/acd12
Rich Text Boxes: 599cd.com/acx5
Arrays: 599cd.com/arrays
Combo Box .Column: 599cd.com/acd15
RESOURCES:
FREE Customer Template Database: 599cd.com/XCDT
FREE Access Level 1: 599cd.com/Free101
$1 Access Level 2: 599cd.com/1Dollar
Your Questions Answered: 599cd.com/Tech...
Learning Connection 50% OFF: 599cd.com/Conn...
Survey: 599cd.com/survey
Richard's Blog: 599cd.com/blog
Access Forum: 599cd.com/Acce...
Twitter: / learningaccess
Email Me: amicron@gmail.com
en.wikipedia.o...
products.offic...
microsoft.com/...
KEYWORDS:
For Next Statement
For Next Loops in Microsoft Access VBA
Count Occurrences of a Character in a String
Count specific character in a string
count the occurrences of a character
Match Parentheses
Match Brackets
Rich Text in Long Text Fields
Add HTML Codes and Color to Text Boxes
Change Font Color in Text Box
When you go off in a tangent, it turns out to be your best TechHelp videos. Thanks again
Oops, meant to post this on your website after watching the Extended Cut video
Ha ha. My tangents are legendary. :)
I never really liked VBA but you seem to be changing my mind....great teachers can do that. Well done Sir!
Glad to hear that! If you haven't watched this yet, do it. Do it. DO IT.... 599cd.com/IntroVBA
How is it possible that you "only" have 184000 subscribers ??? With the quality of your videos - you should have at least a million....
Tell me about it. LOL. The problem is that there aren't anywhere near as many people who use Access as, say, Excel or Word. So the channels that feature Excel videos tend to have a lot more followers. That's OK. I'll focus on quality and do what I do best... Access. I'd rather have a larger slice of a smaller pie. :) I put up an Excel or Word video from time to time, but my passion has always been Access.
This is a great video - I really liked how you incorporated programming into the solution. I particularly like some of the small notes you make, like noting how to use the variables from in the "program" and that there are 2 hidden characters for a line break (is there only one in Excel). This topic reminds me of my Fortran class from '82 but without the punch cards (yes I actually did have to use those things). Thank you!
Glad you enjoyed it! Yeah, I took Fortran in high school, along with Pascal. I learned BASIC myself when I was like 8, so those languages seemed so out of date. LOL
Back in the day when I was only 17 and "Programming" in Microsoft Basic, I had to figure out Special functions like a customer wanting to return an item for credit. I set up my program to recognise the R then the quantity returned. I keep messing up with Access because I still want to add the $ to the vb code. IF LEFT$(Q$, 1) = "R" THEN OD = VAL(MID$(Q$, 2, (LEN(Q$)))) Those darn old habits. :) Thank you for sharing your TechHelp Videos!
There actually is a difference between Left() and Left$(). One can handle variants. The other must be strings.
The CR (carridge Return) and LF (Linefeed) are from the matrix printer ages .. CR returned the carridge to the starting point, the LF fed the paper 1 line up ..
Yeah! I'm old enough to remember that. LOL
I thought you always have to combine an “IF” statement with “End If”, but within the For next loop, you did not use “End If”? So, it is not mandatory?
You can have a one-line IF statement which does not need END IF, such as:
IF X=1 THEN Y=2
Or you can have a Multi-Line IF statement which requires END IF, like:
IF X=1 THEN
Y=2
Z=3
END IF
See 599cd.com/ACD1
You also don't need to use End If if you have multiple lines connected with colons:
If X=Y Then A=1: B=2: C=3
In this case, all three actions after Then are only run if the condition is true. This is a good shorthand if you have short statements, but it is frowned upon if you have long statements that would make the code look messy.
Super
Thanks
How to specific characters count
I get a ton of questions every day, and I don't have time to answer them all here on RUclips. Feel free to submit your question on my website at: 599cd.com/AskYT