Sort by Title ignoring "THE", "A", & "AND"

[Guest]

I am trying to sort a report on a book title. I currently have it query
programmed to read the title, and if the title starts with "The" it will put
the "The" behind the rest of the title. For example. "The book" is changed
to "Book, The" using the following code: SortColumn:IIf(Left([BookTitle],4)
= "The ",Mid([BookTitle],5) & ",
The",[BookTitle])

I would like to do the same thing for "A" or "AN". The code is simple if I
do them each seperatly, how do I put them together so IF it starts with a
THE then do this or If is starts with "A" then do this. etc.?

Thanks!

 

[fredg]

I am trying to sort a report on a book title. I currently have it query
programmed to read the title, and if the title starts with "The" it will put
the "The" behind the rest of the title. For example. "The book" is changed
to "Book, The" using the following code: SortColumn:IIf(Left([BookTitle],4)
= "The ",Mid([BookTitle],5) & ",
The",[BookTitle])

I would like to do the same thing for "A" or "AN". The code is simple if I
do them each seperatly, how do I put them together so IF it starts with a
THE then do this or If is starts with "A" then do this. etc.?

Thanks!

You can nest IIF's.

SortColumn:IIf(Left([BookTitle],4) = "The ",Mid([BookTitle],5) &
",The", IIf(Left([BookTitle],2) = "A ",Mid([BookTitle],3) &
",A",IIf(Left([Booktitle],3) = "An ",Mid( etc....),[BookTitle])))