Word count

[Allan TFF]

I need to automatically determine the number of words in a field (there
shouldn't be more than 10), and to write that value to a field in the same
table. How can I do this, preferably without a query?

 

[Douglas J. Steele]

Assuming there's only one space between each word, the following should tell
you how many words there are:

Len(NameOfField) - Len(Replace(NameOfField, " ", "")) + 1

Alternatively, you could use

UBound(Split(NameOfField, " "))

Both solutions assume you're using Access 2000 or newer.

 

[Allan TFF]

Thanks for the quick response, Doug.

I'm a relative novice on Access - can you tell me how to implement the
expressions below?

(Can I paste one of them into the 'Default value' in the field properties of
the [number_of_words] in the table? or is there another, probably more
complex way?)

Cheers
Allan

In the field properties in my table, do I paste

 

[Douglas J. Steele]

You shouldn't store the word count: it's completely derivable from the data,
so storing it would be a violation of database normalization principles.

Create a query based on your table. Add a computed field to that query by
pasting the following into a blank cell on the Field row:

NumberOfWords: Len(NameOfField) - Len(Replace(NameOfField, " ", "")) + 1

Use the query wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the quick response, Doug.

I'm a relative novice on Access - can you tell me how to implement the
expressions below?

(Can I paste one of them into the 'Default value' in the field properties
of
the [number_of_words] in the table? or is there another, probably more
complex way?)

Cheers
Allan

In the field properties in my table, do I paste

Assuming there's only one space between each word, the following should
tell
you how many words there are:

Len(NameOfField) - Len(Replace(NameOfField, " ", "")) + 1

Alternatively, you could use

UBound(Split(NameOfField, " "))

Both solutions assume you're using Access 2000 or newer.