Alphabetical sort order

[Derwood]

I have a music database listing, amongst other things, recording artists and
track titles. I have two similar problems:
1. When listing tracks alphabetically, those that have a bracket at the
beginning come first; how can I make Access ignore these punctuation marks
and sort by first letter only?
2. How can I make Access ignore the word "The" in band names, i.e. order
"The Stranglers" under S rather than T?

 

[Larry G.]

I am sure there is a complicated answer to this, but have you thought about
not including the puncuation, and listing bands as "Smiths, The"?

 

[Douglas J Steele]

You can try creating a query that has some calculated fields in them to use
rather than the existing fields.

For 1, you could try:

IIf(Left(TrackNm, 1) = "(", Mid(TrackNm, 2), TrackNm)

For 2, you could try:

IIf(Left(ArtistNm, 4) = "The ", Mid(ArtistNm, 5), ArtistNm)

 

[John Vinson]

I have a music database listing, amongst other things, recording artists and
track titles. I have two similar problems:
1. When listing tracks alphabetically, those that have a bracket at the
beginning come first; how can I make Access ignore these punctuation marks
and sort by first letter only?
2. How can I make Access ignore the word "The" in band names, i.e. order
"The Stranglers" under S rather than T?

Well, Access of course has no intelligence and no knowledge of English
usage. A text string is a text string, made of ASCII characters, and
if you want to sort it it will blindly do so on the basis of what is
in the text string.

One possibility would be to include a second, formally redundant,
field in the table just for sorting. This could be filled - manually,
or using VBA parsing code - with the text string you want to use as
your sort key:

The Stranglers STRANGLERS THE
(Reprise) REPRISE
Budapest String Quartet BUDAPEST STRING QUARTET

<etc>

Put an Index (nonunique probably) on this field so that sorting will
be quicker.


John W. Vinson[MVP]

 

[Puppet_Sock]

I have a music database listing, amongst other things, recording artists and
track titles. I have two similar problems:
1. When listing tracks alphabetically, those that have a bracket at the
beginning come first; how can I make Access ignore these punctuation marks
and sort by first letter only?
2. How can I make Access ignore the word "The" in band names, i.e. order
"The Stranglers" under S rather than T?

One approach is validated input when creating the entries in your
database.
So, the uesr enters an item for an existing artist. The artist has to
be
selected from a list. Then the title is filtered before it is stored,
to get rid
of things that are not desired. Or, when entering a new artist, the
entry
is filtered before it is stored, and the filtered name is checked
against
existing entries to see if it is already there.

That way, you never store anything that starts with "the" and you never
have a bracket as the first character of a title, and so on.

You can do this through a form. The area the user enters is not
directly
bound to a table, but is controlled through VB. The app looks at it
when
the user is done, corrects it as necessary, then offers the user a
chance
to change things. If all is well, then it is entered into the table(s).
For
simply browsing through the database you can use a different, possibly
simpler form where the fields are directly bound.
Socks