fuction like dcount in access

[theyad75]

im looking for function can use to count specific word in column from text


example have records like

id movie_title count



1 the good the bad the 3
2 the beauty and the beast 2
3 the rock 1

searching for criteria like "the" should have result

[June7]

That will require VBA custom function that searches for the substring and parses the original string into multiple parts. Like:

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

[theyad75]

well i have vba function and work well as test but when i try the code in query asUser-defined function work but give me wrong answer .

[June7]

Post your function code and the query SQL statement for analysis.

[theyad75]

Public Function getWordCount(ByRef sSentence As String, ByRef sWord As String) As Integer
Dim RegEx As Object
Dim regexMatches As Object
Dim oReturn AsVariant
Set RegEx = CreateObject("VBScript.RegExp")
RegEx.MultiLine = False
RegEx.Global = True
RegEx.IgnoreCase = False
RegEx.Pattern = "[" sWord & "]+"
Set regexMatches =RegEx.Execute(sSentence)
getWordCount = regex
Matches.Count
End Function
-----------------------
SELECT Books.Title, getWordCount([Books]![Title],[Forms]![Form2]![txtCriteria]) AS WordCountFROM Books;

[June7]

Why does your code create a VBScript object? I am not familiar with the code syntax you are using.

Did you try the code I suggested?

This is marked solved - is it?

[theyad75]

yes yes and what you gave me work right amazing

[theyad75]

tell me June7 what about mssql can i use the same code?

[June7]

I don't understand. MS SQL is the backend and you have Access link to it? The code should not care what the tables are.