Sort query by ignoring insignificant words...

[Conan Kelly]

Hello All,

I have a household inventory DB. Some of the inventory includes our movies on DVD. I'm trying to sort the list of DVD's in
ascending order by title. When I sort this, I want insignificant first words to be ignored (A, An, The).

For example, I want "A Knights Tale" to be in the K's, not the A's. Any title that begins with "The" I want to be sorted by the
second word.

I'm looking for a SQL query or VBA code that will help me do this. Does anyone have anything.

I'm sure I could figure this out on my own, but I figured that someone out there probably already has something out there. And if
they are someone that really knows what they are doing, their code/query will probably be more efficient than anything that I can
come up with.

Thanks for any help anyone can offer,

Conan Kelly

 

[Tom Ellison]

Dear Conan:

Now, what they have in common is that they are the "first word" of the
title. The first step would probably be to determine what the first word is
for every title in the table. Use InStr to find the first space in the
title, and then the Left() function to extract everything up to that first
word. If the first word is one of those in the list, then remove that word
and the following space from the title for purposes of sorting the list.

Specifically, you may want this:

ORDER BY IIf(Left(Title, Instr(Title, " ") - 1) IN ("The", "A", "An"),
Mid(Title, Instr(Title, " ") + 1, Title)

If you have any "one word" titles, this may need some modification. When
there's no space in a title it would error.

Tom Ellison