Tags: access, book, database, ignoring, microsoft, mysql, oracle, queryprogrammed, report, sort, sql, starts

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

On Database » Microsoft Access

1,668 words with 1 Comments; publish: Wed, 04 Jun 2008 02:08:00 GMT; (25062.99, « »)

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!

All Comments

Leave a comment...

  • 1 Comments
    • On Tue, 12 Apr 2005 20:13:04 -0700, kknoblauch wrote:

      > 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])))

      Fred

      Please only reply to this newsgroup.

      I do not reply to personal email.

      #1; Wed, 04 Jun 2008 02:09:00 GMT