5/17/2008 3:50:03 AM
Access: Removing THE From Title Sorts
By Richard Rost
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:
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:
Yes, it's in Access 2003 (I don't like 2007 yet) but you should be able to figure it out.
Keywords: access tips removing the iif left right len