count number of instance of a string in a field

[csgraham74]

Hi Guys,

I was wondering if soemone could help me.

Im trying to write a piece of code that queries a field in ms access
for a keyword. what i want to do is count the number of times the
keyword exists in the memo field and store that in a temporary table.

My problem is the query to count the number of times the keyword is in
the field.

can anyone please help me ??

thanks

CG

[Ben]

On Sep 20, 9:52 am, csgraham74 <[e-mail]:

Hi Guys,

I was wondering if soemone could help me.

Im trying to write a piece of code that queries a field in ms access
for a keyword. what i want to do is count the number of times the
keyword exists in the memo field and store that in a temporary table.

My problem is the query to count the number of times the keyword is in
the field.

can anyone please help me ??

thanks

CG

You could use nested instr functions. Just start the next instr after
the substr where it found the last one. You just won't be able to
search for more then 5 or 6 words because the query will be too
complex to run.
e.g.
str = "String to be searched for a string"
iif(instr(0, str, "string")>0, iif(instr(0, instr(0, str, "string") -
(len(str)+len("string"), "string")>0, 2,1),0)

Otherwise you will need to make a VBA function to loop through the
words in the string to find what your looking for.

[fredg]

On Thu, 20 Sep 2007 09:52:59 -0700, csgraham74 wrote:

Hi Guys,

I was wondering if soemone could help me.

Im trying to write a piece of code that queries a field in ms access
for a keyword. what i want to do is count the number of times the
keyword exists in the memo field and store that in a temporary table.

My problem is the query to count the number of times the keyword is in
the field.

can anyone please help me ??

thanks

CG

NumberOfTimes:(Len([FieldName])-Len(Replace([FieldName],"bus","")))/3

Let's say you wish to count the number of times the word "bus" appears
in a field.
THE BUS IS A VERY BUSY BUSINESS. The query would return back a "3".

The /3 part is whatever the number of characters in the search string
(i.e. "bus") is.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[csgraham74]

that is fantastic exactly what im after - i now have to apply this to
some sort of keyword search. ie. to find the records that contain the
greatest number of instances of a certain set of keywords.

i should be able to adapt this - thank-you