This Access tutorial explains how to count substring in string (text).
Access count substring in string (text)
Access does not have a buit-in Function to count substring in string (text), instead you need to work around to get the job done. Alternatively, I have made a VBA solution for your reference.
Access count substring in string using Len and Replace Function
Access Len Function is to measure the length of text.
Access Replace Function is to replace a substring in a text.
To count substring in string, simply replace the target substring with nothing (removing the substring), and then measure the difference of text length before and after replace, and then divide the difference by length of substring.
For example, [str] field contains the string, while [substr] field is the substring.
Access VBA custom Function to count substring in string
I created this custom Function long time ago before I knew the Len and Replace trick, now this is for your reference.
Public Function wCountSubStr(str As String, substr As String) As Integer lenstr = Len(str) lensubstr = Len(substr) For i = 1 To lenstr tempString = Mid(str, i, lensubstr) If tempString = substr Then count = count + 1 End If Next i wCountSubStr = count End Function
Syntax of Custom Function – count substring in string
|str||String to be evaluated|
|substr||Substring that you want to count|
Example of Custom Function – count substring in string