Table View Field that ignores "The, A, An" in titles (of books, movies, songs...)

October 14, 200124 yr

I don't really like using the LeftWords and MiddleWords functions, but they might do the trick in this case. The proviso is that the word you want to eliminate is always at the beginning of the text

FormattedBandName = Case(

LeftWords(BandName,1)="The", MiddleWords(BandName,2,200)&", The",

LeftWords(BandName,1)="A", MiddleWords(BandName,2,200)&", A",

LeftWords(BandName,1)="An", MiddleWords(BandName,2,200)&", An",

BandName)

October 14, 200124 yr

How do I make a field for a table view layout that lists titles of bands that start with "A, The, An" properly?

For example, my field <Band Name> on a form view for a music database lists "The Rolling Stones" (which is how I enter the data and how I like it displayed on the form), but in table view I want a field that would list it as "Rolling Stones, The" so that they are alphabetized in the "R's" not the "T's".

Thanks