计算一个单词在字段中重复的次数

Question

我想从我在MS Access中的表单中计算一个单词在文本字段中重复出现的次数--类似于Excel的=COUNTIF(K2:K100,"*tm-9*")。

我遇到过=Sum(IIf ..，但我需要知道它显示了多少次，包括下面示例中的重复。

​

​

Answer 1

您只需一行代码即可找到计数：

p = "Some string with one tm-9 or more tm-9s"
s = "tm-9"
WordCount = (Len(p) - Len(Replace(p, s, ""))) / Len(s)

WordCount -> 2

Answer 2

没有可以计数的内置函数。尝试创建您自己的函数。这将会起作用，只需分配您自己的字段和标准值，变量- Res将返回您想要的数字。

Dim Field as String
Dim CriteriaText as String
Dim FieldLength as Integer
Dim CriteriaLength as Integer
Dim Res as Integer
dim j as Integer

Res = 0

Field = 'assign the field value here 
CriteriaText = 'assign the criteria her
FieldLength = LEN(Field)
CriteriaLength = LEN(CriteriaText)

WHILE FieldLength >= CriteriaLength 
  j = InStr(Field , CriteriaText)
  IF j > 0 THEN
    Res = Res + 1
    Field = REPLACE(Field, CriteriaText, "", 1, 1)
    FieldLength = LEN(Field)
  ELSE
    CriteriaLength = FieldLength  + 1
  END IF
Loop

MsgBox Res