How can I ignore the articles "a" "an" and "the" when sorting?

[kitcat]

I have a database of books that I want to sort by title, but I want to ignore
the articles "a, an,and the" when sorting. I have been putting the article at
the end of the title in parenthesis, but know there must be another way to
accomplish this.

 

[Douglas J. Steele]

You can add a computed field to the query and sort on it.

Try something like:

IIf(Left([TitleField], 2) = "a ", Mid([TitleField], 3),
IIf(Left([TitleField], 3) = "an ", Mid([TitleField], 4),
Iif(Left([TitleField], 4) = "the ", Mid([TitleField], 5), [TitleField])))

(that all has to go into a single cell in the query builder)

 

[Mark A. Sam]

Kitkat,

I suggest that you create another title field which has the portions of the
title excluding the articles and sort on that. Then you can enter the the
proper titles. You don't need to see the sort field.

God Bless,

Mark A. Sam

 

[Linq Adams via AccessMonster.com]

Actually, the way that it's frequently done is to put the articles at the
beginning of the title, where they belong, but inside of parentheses, i.e.

(A) Tale of Two Cities
(A) Midsummer's Night Dream

so that it "reads" correctly. If you do this, you can create a calculated
field in your query, like this:

SortTitle: Right([BT],Len([BT])-InStr([BT],")")-1)

Substitute the actual name of your "title" field for BT in the code.

This strips the parens and everything in between them. Now simply sort on
this calculated field. The records will then show up as

(A) Midsummer's Night Dream
(A) Tale of Two Cities

 

[KARL DEWEY]

What about using a calculated field like this --
Title_Sort: Replace(Replace(Replace([YourTitleField], "a ", ""), "an ", ""),
"the ", "")

 

[Klatuu]

You would loose the end of every word in the title than ends with
a, an, or the

"The Life Story Of Historian Elma Forsythe"
Would be:

"Life Story Of Histori Elm Forsy"

oops!
--
Dave Hargis, Microsoft Access MVP

What about using a calculated field like this --
Title_Sort: Replace(Replace(Replace([YourTitleField], "a ", ""), "an ", ""),
"the ", "")

--
KARL DEWEY
Build a little - Test a little

I have a database of books that I want to sort by title, but I want to ignore
the articles "a, an,and the" when sorting. I have been putting the article at
the end of the title in parenthesis, but know there must be another way to
accomplish this.

 

[KARL DEWEY]

I do not think it would matter in the sort.
--
KARL DEWEY
Build a little - Test a little

You would loose the end of every word in the title than ends with
a, an, or the

"The Life Story Of Historian Elma Forsythe"
Would be:

"Life Story Of Histori Elm Forsy"

oops!
--
Dave Hargis, Microsoft Access MVP

What about using a calculated field like this --
Title_Sort: Replace(Replace(Replace([YourTitleField], "a ", ""), "an ", ""),
"the ", "")

--
KARL DEWEY
Build a little - Test a little

I have a database of books that I want to sort by title, but I want to ignore
the articles "a, an,and the" when sorting. I have been putting the article at
the end of the title in parenthesis, but know there must be another way to
accomplish this.

 

[Tank]

Hi Kitcat,

Piggybacking on Douglas Steele’s solution, another approach is in form
design instead of query design. If you have a data entry/edit form, you can
achieve your task in a couple of different ways using an AfterUpdate event
procedure.

If you have a text control named “Title†on your form for the table field
with the same name (control source in property sheet for the text control),
here is an event procedure that works for me:

Private Sub Title_AfterUpdate()

If IsNull(Title) Or Not IsNull(Title) And _
Left([Title], 4) = "The " Then
Title = Mid$([Title], 5) & ", The"

ElseIf IsNull(Title) Or Not IsNull(Title) And _
Left([Title], 2) = "A " Then
Title = Mid$([Title], 3) & ", A"

ElseIf IsNull(Title) Or Not IsNull(Title) And _
Left([Title], 3) = "An " Then
Title = Mid$([Title], 4) & ", An"

ElseIf IsNull(Title) Or Not IsNull(Title) And _
IsNull([Title]) Then
Title = Null

End If

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

End Sub

Note the one extra space after the last letter in quotes when writing “A “,
“An “, “The “. This extra space eliminates unwanted chopping off of such
words as “Another†or Theaterâ€, etc.

I personally add a field in my book table called NameKey which stores the
book titles in capital letters and identifies the Book ID in parentheses a
few spaces to the right of the book title. I use the NameKey field to sort
all records alphabetically. Example:

STREETCAR NAMED DESIRE, A (101)
INCONVENIENT TRUTH, AN (102)
STRANGER, THE (103)


I also use the NameKey field in an unbound combo box control on my form to
provide me a drop down list of all book titles allowing me to select a title
and display that record’s information on the current form view.

The actual book title can appear on the form in the Title text control (and
in the table’s Title field) in its normal order:

A Streetcare named Desire
An Inconvenient Truth
The Stranger

The AfterUpdate event procedure for the Title text control stores the
desired formatted information in the NameKey field. Here is the event
procedure that works for me:

Private Sub Title_AfterUpdate()

If IsNull(NameKey) Or Not IsNull(NameKey) Then
NameKey = UCase([Title] & " (" & [BookID] & ")")

End If

If IsNull(NameKey) Or Not IsNull(NameKey) And _
Left([Title], 4) = "The " Then
NameKey = UCase(Mid$([Title], 5) & ", THE (" & [BookID]
& ")")

ElseIf IsNull(NameKey) Or Not IsNull(NameKey) And _
Left([Title], 2) = "A " Then
NameKey = UCase(Mid$([Title], 3) & ", A (" & [BookID] &
")")

ElseIf IsNull(NameKey) Or Not IsNull(NameKey) And _
Left([Title], 3) = "AN " Then
NameKey = UCase(Mid$([Title], 4) & ", AN (" & [BookID] &
")")

ElseIf IsNull(NameKey) Or Not IsNull(NameKey) And _
IsNull([Title]) Then
NameKey = Null

End If

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

End Sub


The NameKey field with Event Procedure is valuable in alphabetically listing
such organizations as AMERICAN RED CROSS, THE , SALVATION ARMY, THE, and
KAISER FOUNDATION, THE, etc.

Hope this helps.
- - - -
Tank