Question:

I have built a database. In this database I have a field name (Note) (Memo type ). I need code so that I can count the words in this field without the spaces.

Answer:

Try the following:

Open the Modules section of Microsoft Access and create a new module.

Paste the following code into this new module:

Public Function fHowManyWords(NameOFField As String)

Dim str As String

str = NameOFField

If Len(str) = 0 Then

    fHowManyWords = 0

Else

    'Start by trimming leading/trailing spaces

    str = Trim(str)

    'Now, while we have 2 consecutive spaces, replace them

    'with a single space...

    Do While InStr(1, str, "  ")

      str = Replace(str, "  ", " ")

    Loop

    Dim aWords

    aWords = Split(str, " ")

    fHowManyWords = UBound(aWords) + 1

End If

End Function

Save this module by clicking on the save icon on the tooolbar. Close the module.

Open a new query.

In the first row of the first column, type the following:

WordNumber: “(“ & IIf(IsNull([Note]),0,fHowManyWords([Note])) & “) WORDS”

This will return a zero if there are no words (Null), otherwise it will run the function and return how many words there are.

Do you like this tip? Subscribe to my free Newsletter to receive tips via email. Click Here to subscribe.