Count # of instances of certain word in Memo Field?

[andmunn]

Hello All,

Is there a function to count the number of instances of a certain word in a memo field?

Searched the forum - can't really find what i'm looking for. (i.e.// find the # of times the word "apple" appears in a string of words in the memo field).

Andrew.

 

[The_Doc_Man]

For the absolutely general case, where the word in question could appear more than one time in the same field and must be counted each time as a separate instance (i.e. seeking "apples" and the Memo says "This is an apples-to-apples comparison" so you report 2), the only solution I can think of would require VBA code.

In that case, you might write a function in VBA, put it in a general module, and have it return the count of instances for a given memo field. Make reference to the function from a query. Then you can write a summation query based on the first query.

The problem will be getting the value that you want to count into the function as an argument. That part of the problem isn't clear from your statement.

If the counting rules are different than as stated above, there is a chance that you might get away with a less strenuous query. But the problem of defining the keyword to be sought still is an issue for consideration.

 

[andmunn]

Thanks for pointing out the above - i "actually" only need to capture if the word exists in the "field"... Rather than counting the number of times it appears. I.E// whether the word "apples" appears once or twice is irrelevant, i'd just want it to count "1" because the word does appear in the field....

Does that make the query any less complex?

 

[Mr. B]

Try using the InStr() function. It will return the location where the value being searched for starts. You can uses this to evaluate where the word is there or not.

HasMyWord: IIF (Instr(1,"StringToSearch", "StringSearchedFor") > 0, 1, 0)

You can use the above in a query.

If you need the resultsin VBA then you can use the Instr() function in VBA code as has been previously suggested.

 

[andmunn]

Ok, that seems to have done the trick....however...

I created "qryCommonResponses" to track certain words within a certain memo field... it returns the results i want:

I.E//
The word "Coach" (or some derivitive of it) appears 3 times
The word "Staff" (or some derivitive of it) appears once.

However, i want to display this as a summary in "subfrmCommonResponses" - but not sure how to do this...

I want this subfrm to look like:

Coach: 3
Staff: 1

But can't seem to get it to show correctly? (not sure how to specificy which count it needs to return).

Any help? (thanks again!) i've attached the Database.

Andrew.

 

[Mr. B]

Turn your query into a "Totals" query and have each of the colums where you are getting the count as a sum. That should give you the total for each.

 

[andmunn]

That did the trick - switching it over to "sum".... Just lost my mind there for a second - thanks so much.

Andrew.

 

[Mr. B]

You are quite welcome. Glad to help.