Q: I am creating a database of my DVD collection in Access 2007 and trying to have them sorted in alphabetical order. How do I write an expression that will not include the word "The" at the beginning of the title when it sorts them?


A: GOOD question.

You would need to create a CALCULATED QUERY FIELD to remove "THE" from the beginning of your title. Something like:

NoThe: IIF(Left(MyTitle,4)="The",Right(MyTitle,Len(MyTitle)-4),MyTitle)

Or you could even make one that tacks the "The" on the end...

NoThe: IIF(Left(MyTitle,4)="The",Right(MyTitle,Len(MyTitle)-4) & ", The",MyTitle)

Oooh yeah, I like that second one. Now drop this into a query as a CALCULATED FIELD, and then base your sort on that field. If you need help with creating calculated queries, here's a free tutorial for you:

/tips/access/calculated-query-fields

Yes, it's in Access 2003 (I don't like 2007 yet) but you should be able to figure it out.

Enjoy.

Subscribe to Access: Removing THE From Title Sorts
Get notifications when this page is updated