Word count from memo field query help

[Willis]

I would like to create a query that counts the number of instances of a word in a memo field but im not sure how to get it setup.

Ive tried SELECT COUNT (*) FROM [Evaluation Table]WHERE[Opened Call] = 'Yes' but its not working.

Any help would be greatly appreciated

[rt_roh]

Can't think of a simple solution or any function that returns count of instances.
Meanwhile you will have to create a function in module and set the control source property of the count textbox to
=CountInstances(IIf(IsNull([FLd name]),"",[FLd name]),'YES')

The code for function is
-----------------------
Public Function CountInstances(strInput As String, strToFind As String)
Dim iCtr As Integer
Dim iCnt As Integer
Dim iPos As Integer
iPos = InStr(1, strInput, strToFind, vbTextCompare)
Do While iPos > 0
iCnt = iCnt + 1
iPos = InStr(iPos + Len(strToFind), strInput, strToFind, vbTextCompare)
Loop
CountInstances = iCnt
End Function
---------------------------------
OR if in query simply write

select CountInstances(IIf(IsNull([FLd name]),"",[FLd name]),'YES')
from [table name]

Hope it is helpful.

[Willis]

I really appreciate the response =)

I created the function and then attempted to create a query and in the field I input

(SELECT CountInstances(IIf(IsNull([Opened Call]),"",[Opened Call]),'YES')FROM [Evaluation Table])

which returned the error

"At most one record can be returned by this subquery"

Where did I go wrong?

Thanks again

[rt_roh]

I think you are to assign the result of this function to a text box. If trying to assign this to a text item on the form, simply set the "control source" property of the textbox(or field) to
=CountInstances(IIf(IsNull([FLd name]),"",[FLd name]),'YES')

Don't need to create query for textbox assignment.

And query I had mentioned just to show that u can use module functions in queries too..

[Willis]

I was handed a database and told to change it so we could add more objects to the form since it was full so i wanted to change radial dial buttons into list boxes which wrote a string of the selected answers to a memo field. I now need to count the number of words that are specific to certain answers in order to count the number of selected answers being input into the memo field so I can report that information. So i want to do it with out adding objects for inputting to the table. Is there a way to use a query to count word instances as a way of calculating selected answers.

Thanks again

[rt_roh]

It took me quite a while to reconstruct your subquery error.

In the Query field you must paste just:-
CountInstances(IIf(IsNull([FLd name]),"",[FLd name]),'YES')

DO NOT write the complete select _ _ _ _ _ '.

Apart from your error:-
There are 2 ways you will try to get your result:
1. In a query.
2. To be displayed on the form.

To be shown thru qry use the qry syntax
OR
If u have to display it on the form, then set the control source property of the unbound textbox.(unbound means it is not attached to any table)

[Willis]

Its working exactly like I need it to. I cant thank you enough.

[Adrian_Sayers]

Hi

This sounds just like the soloution i need, but i cant make it work
I have quite limited access ability, but i have done this so far:

created a module and copied this into it:

Public Function CountInstances(strInput As String, strToFind As String)
Dim iCtr As Integer
Dim iCnt As Integer
Dim iPos As Integer
iPos = InStr(1, strInput, strToFind, vbTextCompare)
Do While iPos > 0
iCnt = iCnt + 1
iPos = InStr(iPos + Len(strToFind), strInput, strToFind, vbTextCompare)
Loop
CountInstances = iCnt
End Function

and saved it as CountInstances

I then in a query i copied into the field: section

CountInstances(IIf(IsNull([FLd name]),"",[FLd name]),'YES')

and i am now stuck?? Help

I am looking for the word "interview*" in a field called Title

any chance you can exemplify it a bit more.

Sorry for being really thick!

[Willis]

First of all you need to add the field your querying from to the query. So in the first Column field select Title from the table. In the column next to it in the Field box put this:

CountInstances(IIf(IsNull([Title]),"",[Title]),'interview')

thats assuming the field you want to search is named Title and the word your looking for is interview

hope this helps you out

NOTE: you can uncheck the column that has the field reference for the query so that when executed you cant see it

also realize its not going to total the count youll have to build an additional query from this one that you use to get the sum of each count

[Adrian_Sayers]

Hi,

Okay got the thing to work as it should with the exception of including wild cards into the statement.

For example if i search telephone interview, it will find any truncation of interview but not telephone e.g. telephone interviewer is okay but telephoneing interviews is not.

(I know the english makes no sense)

Any suggestions of how to build on the loop to take wild cards?

Any help would be greatly appreciated.

Cheers Adrian