Sort text ignoring Titles with "The" or "A" / "An"

[Guest]

I have a set of titles of movies, with "The" or "A" in the front of the
title. I would like to ignore these definite/indefinite articles when I sort
the titles alphabetically.

I had thought about entering data using the "FileTitle, The" method, but
this looks a bit clunky and I like having the title dislayed properly with
each record.

Is there some easy code that ignores certain words before a sort, or do I
need to do a bunch of string manipulation?

Any suggestions would be appreciated. Thank you.

 

[fredg]

I have a set of titles of movies, with "The" or "A" in the front of the
title. I would like to ignore these definite/indefinite articles when I sort
the titles alphabetically.

I had thought about entering data using the "FileTitle, The" method, but
this looks a bit clunky and I like having the title dislayed properly with
each record.

Is there some easy code that ignores certain words before a sort, or do I
need to do a bunch of string manipulation?

Any suggestions would be appreciated. Thank you.

You can do this easily in a query.
Add a new column to your query.

SortField: IIf(Left([Title],4)="The ",Mid([Title],4) & ",
The",IIf(Left([Title],2)="A ",Mid([Title],3) & ", A",[Title]))

Sort your query on this field.

 

[Guest]

Thank you! I'll give it a shot.

I have a set of titles of movies, with "The" or "A" in the front of the
title. I would like to ignore these definite/indefinite articles when I sort
the titles alphabetically.

I had thought about entering data using the "FileTitle, The" method, but
this looks a bit clunky and I like having the title dislayed properly with
each record.

Is there some easy code that ignores certain words before a sort, or do I
need to do a bunch of string manipulation?

Any suggestions would be appreciated. Thank you.

You can do this easily in a query.
Add a new column to your query.

SortField: IIf(Left([Title],4)="The ",Mid([Title],4) & ",
The",IIf(Left([Title],2)="A ",Mid([Title],3) & ", A",[Title]))

Sort your query on this field.

 

[Guest]

I wanted to provide some follow-up.

When I originally tried the code, the title would be modified into the
correct "Title, The" format. However, the title would have a leading blank
space in front, and so still not sort the intended way.

After some experimentation I discovered that if you increase the number of
characters by one from the "Left" function to the "Mid" function, you get the
correct result.

Below is the final text which seems to work just fine. Again, thank you for
your help. I am still new to this stuff - - glad this works - but I would
like to learn eventually WHY it works....


Final code (for titles with The, A or An in the front):

SortField: IIf(Left([Title],4)="The ",Mid([Title],5) & ",
The",IIf(Left([Title],2)="A ",Mid([Title],3) & ", A",IIf(Left([Title],3)="An
",Mid([Title],4) & ", An",[Title])))

Thank you! I'll give it a shot.

I have a set of titles of movies, with "The" or "A" in the front of the
title. I would like to ignore these definite/indefinite articles when I sort
the titles alphabetically.

I had thought about entering data using the "FileTitle, The" method, but
this looks a bit clunky and I like having the title dislayed properly with
each record.

Is there some easy code that ignores certain words before a sort, or do I
need to do a bunch of string manipulation?

Any suggestions would be appreciated. Thank you.

You can do this easily in a query.
Add a new column to your query.

SortField: IIf(Left([Title],4)="The ",Mid([Title],4) & ",
The",IIf(Left([Title],2)="A ",Mid([Title],3) & ", A",[Title]))

Sort your query on this field.

 

[Tim Ferguson]

I have a set of titles of movies, with "The" or "A" in the front of
the title. I would like to ignore these definite/indefinite articles
when I sort the titles alphabetically.

I would seriously consider having a separate column for SortingName and
sorting on that. In this way you would have full freedom to make stuff
appear in the right place. Where would you want "Nineteen-Eighty Four", for
example, or "The 3 Amigos" or "Theatre of Blood" or "A is for Adultery"?
It's one of those areas where a bit of human intelligence will be far more
reliable than a cobbled up bit of AI in VBA.

Hope that helps


Tim F

 

[Guest]

That's a great suggestion and is instructive of not over-programming.

I might make a yes/no switch on the form which overrides the programming so
that for those cases it just uses the Title as originally entered...

Thank you.

 

[Tim Ferguson]

I might make a yes/no switch on the form which overrides the
programming so that for those cases it just uses the Title as
originally entered...

Why bother? It's easy enough to use the data entry form to copy the Title
over to the SortingTitle field if there is nothing in the latter. And it'll
save all those awful IIf(....) clauses in the queries.

All the best


Tim F