Trim after 50 characters, but also back to last white space.

[btappan]

I have some fields that need to be trimmed to 20 characters, but I don't want any cut off mid word, is there a way trim down to 20 characters, but also go even further back to the last blank space before the 20th character?

GREEN BLACK RED GRAY would stay the same


BLACK RED ORANGE YELLOW would become BLACK RED ORANGE instead of BLACK RED ORANGE YEL

[JoeM]

In a calculated field (in a Query or on a Report of Form), for a field named "Field3", it might look something like this:

Trim(IIf(Len([Field3])<=20,Left([Field3],20),Left([Field3],InStrRev(Left([Field3],20)," "))))