Automatically Modifying values

[RLH3]

Hopefully this is the last major problem on this project. I am building a database of books that I want to examine (basically a want list) and would like to sort the title on the first relevant word in the title.

An example:

The Great Raid on Cabanatuan

I would like to sort on "Great" rather than "The."

I figure the easiest way to do this would be to have the "The" moved from the front of the entry to the back after a comma. The end result looking like this:

Great Raid on Cabanatuan, The

This would be my ideal solution, as I can use that field in other places in the layout.

I have fought with this off and on for quite a while now, and have never managed to solve it. I am sure that it is relativly simple and I have just managed to miss it.

I hope that somebody has a solution for this.

Thanks to all in advance,

Roman

[-Queue-]

How about

Case( PatternCount( "a an the", LeftWords( field, 1 )), Right( field, Length(field) - Length(LeftWords( field, 1 )) ) & ", " & LeftWords( field, 1 ), field )

This should retain punctuation in the title. If punctuation isn't an issue, you can also use

Case( PatternCount( "a an the", LeftWords( field, 1 )), RightWords( field, WordCount(field) - 1 ) & ", " & LeftWords( field, 1 ), field )

[Slobey]

You can create a calc field based on your book name:

Case(LeftWords (Book Name; 1 ) = "The";

Substitute (Book Name ; LeftWords(Book Name;1) ; "" ) & ", " & LeftWords(Book Name;1);

Book Name)

Using the "leftwords" function throughout will allow you to use the "or" function for words other than "The"

[Slobey]

Queue,

Is there a limit to the number of words you can place in the patterncount?

[RLH3]

Thanks Queue, Worked great. One last question, How do I get rid of the leading space on the calculation result? It seems to be defeating me.

Roman

[-Queue-]

I assume there is a 256 character limit, as there usually is with text strings in a calculation. So yes, there is a limit, but I doubt it will be an issue most of the time.

[-Queue-]

Whoops! The Right function needs a minus one at the end of it.

Right( field, Length(field) - Length(LeftWords( field, 1 )) - 1 )

[RLH3]

Perfect!

Thanks

Roman