ignore the word "the" at the beginning of a field for sort

[ultrarunner2017]

Hi;
I have my music database in MS Access 2010, and would like to sort artists but leave out "the" in the artist name. I can do an advanced filter, but how do I make the decision of whether or not to mask the first word of the field or not?
For example, to create an alphabetical sort of names that include "The Rolling Stones", "R.E.M"., "Rush", "The Beatles", "Green Day", etc, and have "The Beatles" come up as the first in this sort.


I want to sort but leave out the "The" so that my alphabetic list makes sense, and I don't wind up with a lot of "The" artists in one spot.
Other apps like Music Bee will do this for me, but I have reasons for wanting to keep a separate database in Access.

I'm thinking I need to write SQL code, but if I can do it without the SQL I would like to save myself some time.

Of course I can leave out "The" in the artist names, but I was hoping for a way to avoid that.

Thanks

FW

[Micron]

There will never be any artists in your library where "The" is not at the beginning but is somewhere else? I think the possibility or desired outcome will determine the approach. To both "see" those with The and "order by" without it and leave The wherever else it may be, I think a query that shows the artist field but sorts on what comes after "The" in the same but hidden field (thus the field is in the query 2x, but the Order is on the one you don't see) would be the answer. To drop The from the beginning, use the Mid function. To remove The altogether use the Replace function. I think you'd choose Mid, as in

Artists:Mid([Artist],5) where [Artist] is the name of your field. Note that you cannot use an alias for the field name that is the same as the field, so that's why I typed it that way. Your field may not be named Artist.

[ssanfu]

Using Micron's suggestion, I used:

Artists2:IIF(Left([Artist],4) = "The ",Mid([Artist],5), [Artist])

The query is

SELECT SongArtist.Artist, IIf(Left([Artist],4)="The ",Mid([Artist],5),[Artist]) AS Artists2
FROM SongArtist
ORDER BY IIf(Left([Artist],4)="The ",Mid([Artist],5),[Artist]);

Results:

[Micron]

Thanks for filling in my huge gap!
Not sure what to do about Mott THE Hoople though. Maybe there's few examples of that sort so it's nothing to worry about, given the names they use these days.

[ArviLaanemets]

Simply add a field Alias to artists table. So whenever you enter an artist, you fill a field Artist and optionally a field Alias, p.e.

Artist                        Alias
The Beatles               Beatles
Green Day
The Rolling Stones     Rolling Stones

Now you can sort by Nz(Alias,Artist)

[ultrarunner2017]

Using Micron's suggestion, I used:

Artists2:IIF(Left([Artist],4) = "The ",Mid([Artist],5), [Artist])

The query is

SELECT SongArtist.Artist, IIf(Left([Artist],4)="The ",Mid([Artist],5),[Artist]) AS Artists2
FROM SongArtist
ORDER BY IIf(Left([Artist],4)="The ",Mid([Artist],5),[Artist]);

That should work just fine. Thanks much!
But I am just a bit rusty on Access code:
Where do I write the first part: "
Artists2:IIF(Left([Artist],4) = "The ",Mid([Artist],5), [Artist])" Is this like declaring a variable and setting its value?
Assuming the Query code is the query itself.

[ultrarunner2017]

Yup; That will work as well. Matter of fact, I had to create an alias field for Album Title, so that I can record the original album a track is from. The problem is that I purchase many tracks from iTunes, and then need to burn them to an audio CD so that I can then rip them into a format that will play on my Android phone (I don't want to use the iTunes app for Android).
But writing the short code snippet will help to refresh my memory for Access code, and get me started on more interesting Access coding.

[ssanfu]

Where do I write the first part: "Artists2:IIF(Left([Artist],4) = "The ",Mid([Artist],5), [Artist])" Is this like declaring a variable and setting its value?

No VBA.... except for the functions.
In a query that is in design view, you would add a calculated column like this (type it in the FIELDS row):

Artists2:IIF(Left([Artist],4) = "The ",Mid([Artist],5), [Artist])

Assuming the Query code is the query itself.

In a query that is in SQL view, you have to enter it using the "AS" keyword to add the alias.

SELECT SongArtist.Artist, IIf(Left([Artist],4)="The ",Mid([Artist],5),[Artist]) AS Artists2
FROM SongArtist
ORDER BY IIf(Left([Artist],4)="The ",Mid([Artist],5),[Artist]);

The bold BLUE is the alias name.....

[ultrarunner2017]

Thanks. I really should make use of the several Access resources I have. There is much I could do, that I am now doing manually.