find a word using txtcriteria and count the number of matching criteria

[theyad75]

hello i need help in(

query criteria do count the number of matching search

i use ( Like "*" & [txtcriteria] & "*") but when i have 2 matching search in one record the count is 1

example text : the good the bad the ugly

if i search for "the" the count is one but i have 3
hope the idea is clear)

what im looking is the count of the matching txtcriteria


i found countif in excel and its work ,what about access how i can solve this problem plz help

[jforbes]

A Like statement is used to determine if a Row is to be returned as part of a RecordSet for a SQL Select. So it is working as it should to let you know that the Row has the word you are looking for.

What you'll need to do for each Row it to perform a Count on the particular Field. There is no native way to do that, that I know of, so you will have to resort to programming. This function will give you the Count:

Public Function getWordCount(ByRef sSentence As String, ByRef sWord As String) As Integer
    Dim RegEx As Object
    Dim regexMatches As Object
    Dim oReturn As Variant
 
    Set RegEx = CreateObject("VBScript.RegExp")
    RegEx.MultiLine = False
    RegEx.Global = True
    RegEx.IgnoreCase = False
 
    RegEx.Pattern = "[" & sWord & "]+"
    Set regexMatches = RegEx.Execute(sSentence)
 
    getWordCount = regexMatches.Count
End Function

You can then include the function as part of your query, like:

SELECT Books.Title, getWordCount([Books]![Title],[Forms]![Form2]![txtCriteria]) AS WordCount
FROM Books;

[theyad75]

its work but incorrect answer:for the word "the"

Title WordCount
the good the bad the ugly 3
the beauty and the beast 6
the rock 1
superman 1

[jforbes]

Hmm, It looks like your copy and paste got some extra characters in there? Maybe try to remove the " " out of the code?

[theyad75]

yes right i did remove the " " out of the code ,actualy runing your code in vba (immediate window) work good and nice but when i use this code with query give me wrrong answer

plz see attach pic

[theyad75]

Actualy if someone interest this is the right code . anyway thanx jforbes for your try

Function CountSubstring(strInput As String, strSearch As String) As Integer
While InStr(strInput, strSearch) > 0
    strInput = Mid(strInput, InStr(strInput, strSearch) + Len(strSearch) + 1)
    CountSubstring = CountSubstring + 1
Wend
End Function