Count Words in a Field

[novel]

Is it possible to use MS Access to count the number of words
in a data field?

[NeoPa]

Is it possible to use MS Access to count the number of words
in a data field?

I don't know of a way short of writing a function to process through the data character by character.

[southoz]

Good ay ,
try using the len function to get the length of the string then use a repeating instr function to find all the spaces in the string, that would give u a proper word count

southoz

[Andrew Thackray]

This function counts the number of words in text. Note it treats hyphenated words as two words. If you don't want this remove the hyphen case from the select function.

Function WordCount(ByRef Str As String) As Long
 
    Dim cnt As Long
    Dim Words As Long
 
    Words = 0
    WordCount = 0
    If Len(Str) = 0 Then
        Exit Function
    End If
    For cnt = 1 To Len(Str)
        Select Case Mid(Str, cnt, 1)
            Case " "
                Words = Words + 1
            Case "."
                Words = Words + 1
            Case ","
                Words = Words + 1
            Case ";"
                Words = Words + 1
            Case ":"
                Words = Words + 1
            Case "-"
                Words = Words + 1
        End Select
    Next cnt
    If Words = 0 Then Words = 1
    WordCount = Words
End Function
 

[Killer42]

Just a point - the Instr method, might be slightly more complex to code (though not difficult), but would likely execute faster (except possibly when dealing with very short strings).

Performance would only be an issue, of course, if you are driving it very hard - that is, hitting it for millions of strings, or something.

[Andrew Thackray]

The instr method would be quicker though more complex. however if the function has to deal with punctuation other than spaces betweeen words then it gets very much more complex and probably would not be much quicker.

[NeoPa]

Andrew,

Try the Case statement this way for brevity.

-Adrian.

        Select Case Mid(Str, cnt, 1)
            Case " ", ".", ",", ";", ":", "-"
                Words = Words + 1
        End Select

[Andrew Thackray]

Neat, I forgot I can use lists

[Killer42]

Andrew,
Try the Case statement this way for brevity.
-Adrian.

        Select Case Mid(Str, cnt, 1)
            Case " ", ".", ",", ";", ":", "-"
                Words = Words + 1
        End Select

Nice one. However, this will double count in many cases. A full stop, for example (a "period" to you Yanks) is usually going to be followed by at least one space. It might actually produce a more useful count if you don't look for all the punctuation, but just count gaps. For a really accurate count you might need a more complex routine.

The instr method would be quicker though more complex. however if the function has to deal with punctuation other than spaces betweeen words then it gets very much more complex and probably would not be much quicker.

Good point, I didn't really think about that. For just spaces, Instr is almost certainly, as discussed, a little more complex to code and a little faster to execute. For a more thorough search including commas, colons and so on, I would avoid the Instr method.

[Andrew Thackray]

Damn, I didn't think of the puctuation - space case. I'll have to amend the routine to fix that.

It strikes me that this is a useful function to have in my standard library so its worth the effort to get it right.

[NeoPa]

I wasn't going to mention it, but if it's an oft-used routine, you want to use a flag which remembers whether you're currently in word mode or between words mode. Hope that helps.
(It's good posting for you guys as you don't have to spell everything out in fine details :) )

[pks00]

What if u used the Split command?

 
Dim sWords() as String
 
sWords = Split(myfield," ")
 
msgbox "Number of words is " & ubound(sWords)
 
 

[Killer42]

What if u used the Split command?

Dim sWords() as String
sWords = Split(myfield," ")
msgbox "Number of words is " & ubound(sWords)
 

Nice shortcut, that - I like it!
I think the basic question that this thread hinges on is whether you want to take notice of punctuation or not. My feeling would be that to generate a word count you have two basic options.

• If you are only interested in gaps/spaces to delineate "words" (which would be my recommendation), Split is probably your best bet.
• If you want to take all sorts of punctuation into account, you probably need to loop through character by character and code something like the Select Case.

[NeoPa]

Nice one pks00, and/but Killer is spot on too.

[Andrew Thackray]

Never knew there wuz a slpit function.

However nothing new under the sun. Looking up split on the net resulted in this set of handy routines, including wordcount.

http://j-walk.com/ss/excel/tips/tip93.htm

[Killer42]

Never knew there wuz a slpit function. However nothing new under the sun. Looking up split on the net resulted in this set of handy routines, including wordcount. http://j-walk.com/ss/excel/tips/tip93.htm

Definitely bookmarked - thanks.

[PEB]

Very good exemple pks00!

The function that i've use was:

Function word_count(my_sentence, sep) As Integer
Dim result As Integer
Dim my_str
result = 1
Do While InStr(1, my_str, sep) > 0
result = result + 1
my_str = Mid(my_str, InStr(1, my_str, sep) + 1, Len(my_str))
Loop
word_count = result
End Function

But yours it's great!

Best regards!

What if u used the Split command?

 
Dim sWords() as String
 
sWords = Split(myfield," ")
 
msgbox "Number of words is " & ubound(sWords)
 
 

[novel]

Thank you all for your input! I appreciate the help. However - and I know this might be a stupid question - but how would I use this code in Access????

[PEB]

:)

Ok my friend, we've done a big discussion about your problem...

But you need to select a function it's better the suggestion of pks00 copy from thescripts and

in access go in modules, choose New module and Paste there the function

Save it...

To obtain the word count in a column in your query:

Create a query add your table or query on which should be based the new one.

In an empty column write the name of the choosen function and between the () instaed the name inside, put the name of the column that contain the text that you want to get the count

[stevepsft]

Here's a simple function

[stevepsft]

public function word_count(InWord as variant) as Integer
dim words() as string
 
word_str=nz(word_str,"")   'deals with null values passed in
word_str = replace(word_str,vbcr," ")  ' Whatever you want to also delineate a word can be placed here.
 
while instr(word_str,"  ") > 0 )  'Check to see if double spaces
   word_str = replace(word_str,"  "," ") ' change double to single space
wend
words = split(word_str," ")
word_count = ubound(words) + 1 
end function

[ahmedtharwat19]

pks00 Good statement

Thank you all
Ahmed