String function (counting string within a string)

[Ralph Noble]

Does anyone know of a string function in Access that will allow me to
count the number of instances one string occurs within another? Or if
there is some sort of word count function? If there is, I'm not aware
of it. I've tried the following unsuccessfully ...

SELECT *, (Len([myfield])-Len(Replace([myfield],'
what_im_looking_to_count','')))/(Len(' what_im_looking_to_count')) AS
KeywordFoundCountFROM tblYourTable;

That'll find how many times the phrase what_im_looking_to_count occurs
within the field called 'myfield'

Thanks,

Ralph Noble
ra*********@hotmail.com

[Douglas J. Steele]

What error do you get trying to use that query?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Ralph Noble" <ra*********@hotmail.com> wrote in message
news:ed**************************@posting.google.c om...

Does anyone know of a string function in Access that will allow me to
count the number of instances one string occurs within another? Or if
there is some sort of word count function? If there is, I'm not aware
of it. I've tried the following unsuccessfully ...

SELECT *, (Len([myfield])-Len(Replace([myfield],'
what_im_looking_to_count','')))/(Len(' what_im_looking_to_count')) AS
KeywordFoundCountFROM tblYourTable;

That'll find how many times the phrase what_im_looking_to_count occurs
within the field called 'myfield'

Thanks,

Ralph Noble
ra*********@hotmail.com

[Eric Schittlipz]

"Ralph Noble" <ra*********@hotmail.com> wrote in message
news:ed**************************@posting.google.c om...

Does anyone know of a string function in Access that will allow me to
count the number of instances one string occurs within another? Or if
there is some sort of word count function? If there is, I'm not aware
of it. I've tried the following unsuccessfully ...

SELECT *, (Len([myfield])-Len(Replace([myfield],'
what_im_looking_to_count','')))/(Len(' what_im_looking_to_count')) AS
KeywordFoundCountFROM tblYourTable;

That'll find how many times the phrase what_im_looking_to_count occurs
within the field called 'myfield'

Thanks,

Ralph Noble
ra*********@hotmail.com

In what sense was your attempt to use this function unsuccesful? The
function really does count the occurences of one string within another. For
example, if you had tblMyTable with fields MyID and MyText and wanted to
count how many times the word 'pen' occurs in that field:

SELECT tblMyTable.MyID, tblMyTable.MyText,
(Len(MyText)-Len(Replace(MyText,'pen','')))/(Len('pen')) AS
KeywordFoundCount
FROM tblMyTable

I can see some problems you might encounter, such as when counting the
occurences of the word "pen" in the sentence "I spent 4 dollars on that
pen." The function returns 2 (not 1) because "pen" occurs within the word
"spent"

[Bruno Campanini]

"Ralph Noble" <ra*********@hotmail.com> wrote in message
news:ed**************************@posting.google.c om...

Does anyone know of a string function in Access that will allow me to
count the number of instances one string occurs within another?

Try this:
----------------------------------------
Public Function AAA(StringToSearchIn As String, StringToSearchFor As String)
As Integer
Dim i As Integer, k As Integer, h As Integer
For i = 1 To Len(StringToSearchIn)
h = InStr(i, StringToSearchIn, StringToSearchFor)
If h Then
k = k + 1
h = h + 1
i = h
End If
Next
AAA = k
End Function
-----------------------------------------

Ciao
Bruno

[Eric Schittlipz]

"Ralph Noble" <ra*********@hotmail.com> wrote in message
news:ed**************************@posting.google.c om...

Does anyone know of a string function in Access that will allow me to
count the number of instances one string occurs within another? Or if
there is some sort of word count function? If there is, I'm not aware
of it. I've tried the following unsuccessfully ...

SELECT *, (Len([myfield])-Len(Replace([myfield],'
what_im_looking_to_count','')))/(Len(' what_im_looking_to_count')) AS
KeywordFoundCountFROM tblYourTable;

That'll find how many times the phrase what_im_looking_to_count occurs
within the field called 'myfield'

Thanks,

Ralph Noble
ra*********@hotmail.com

In what sense was your attempt to use this function unsuccesful? The
function really does count the occurences of one string within another. For
example, if you had tblMyTable with fields MyID and MyText and wanted to
count how many times the word 'pen' occurs in that field:

SELECT tblMyTable.MyID, tblMyTable.MyText,
(Len(MyText)-Len(Replace(MyText,'pen','')))/(Len('pen')) AS
KeywordFoundCount
FROM tblMyTable

I can see some problems you might encounter, such as when counting the
occurences of the word "pen" in the sentence "I spent 4 dollars on that
pen." The function returns 2 (not 1) because "pen" occurs within the word
"spent"