Returning Count of Words in a Text Field

[Guest]

I would like to truncate a text string, not just by character length, but
also taking into consideration not truncating in the middle of the last word
in the string. Therefore, here is a sample situation:

1. I want to truncate at 25 characters from the left.
2. However, I want to truncate earlier in order to accomodate the last
complete word.

Example:

1. ORIGINAL STRING: Sally sells sea shells down by the seashore. (44
characters total)
2. SIMPLE TRUNCATION AT 25 CHARACTERS: Sally sells sea shells do
3. TRUNCATION <=25 BUT WITH LAST FULL WORD: Sally sells sea shells

Can someone help me with this last scenario. I've done it successfully in
FileMaker Pro, as I found a function to get the number of words in a string.
Is there something as easy in Access?

 

[Douglas J Steele]

Try the following:

Left(MyString, InStrRev(MyString, " ", 26))

 

[Ron Weiner]

This doesn't return the number of words but it does truncate the string at a
word boundary that is equal to or less than the number specified. What is
really cool is that it is a one liner.

Try this all on one line from the Immediate window...

n=25 : s= "Sally sells sea shells down by the seashore" : ? iif(len(s) <= n,
s, left(s, instrrev(left(s,n +1)," ")))

Prints...

Sally sells sea shells

 

[Guest]

Once you get the first 25 chars into a strTemp variable, use InStrRev
Function to find the location of the last " " char, then just re-do the Left
Function and Trim the string.

 

[Guest]

Thanks! This works out great.

This doesn't return the number of words but it does truncate the string at a
word boundary that is equal to or less than the number specified. What is
really cool is that it is a one liner.

Try this all on one line from the Immediate window...

n=25 : s= "Sally sells sea shells down by the seashore" : ? iif(len(s) <= n,
s, left(s, instrrev(left(s,n +1)," ")))

Prints...

Sally sells sea shells

 

[Guest]

Thanks! This is helpful.

Try the following:

Left(MyString, InStrRev(MyString, " ", 26))

 

[Guest]

Thanks! The InStrRev function was what I was looking for to perform this kind
of operation.