Counting the Number of Words in a field in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a directory that I am compiling through an Access database. Each
exhibitor is allowed a 25-word description in the directory. Is there a way
I can count the number of words in the description field (it is a memo field)
so that I can send a notice to them telling them to shorten their description?
 
The following assumes that all words are separated by single spaces. If you
want to get much more sophisticated than that, you'll likely have to walk
the string examining individual characters, which could be slow with large
volumes of data ...

Public Function CountWords(ByVal varInput As Variant) As Long

Dim varWork As Variant

If Len(Trim$(varInput & vbNullString)) = 0 Then
CountWords = 0
Else
varWork = Split(CStr(varInput), Space$(1))
CountWords = UBound(varWork) + 1
End If

End Function

SELECT Employees.LastName, Employees.FirstName, CountWords([Notes]) AS Words
FROM Employees;

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Back
Top