Ignoring articles when sorting

[SethLibrary]

I have a database of books . In the field for titles How can I get access to ignore articles at the beginning of the titles such as "the" "A" and "An". For example instead of Access sorting on the word "The" in The Raven, I want it to sort on "Raven".



Thanks for your help and patience

[alansidman]

I would do my sorting in a query. I would add a Field to the query with an expression that trims the "The", "A" or "An" and sort on this column/Field. Make the new field hidden by unchecking the display checkbox in the query design mode. Your expression will probably be an nested =IIF statement.

[orange]

Along the same line as Alan's thinking, you could have a table of "noise words" ---words that you don't want in your titles.
Parse each Title, recording the book IBSN or whatever PK you use, then drop all the noise words. Save the revised/processed Title.
If you want to find books by keyword (also eliminating Noise words), record the IBSN and each of the Keywords in the title.
eg
IBSN 3456789 --original title The Immortal Life of Henrietta Lacks

----noise words a, an, as, the, of, in, on, by, for, with, to, from, that, which, who.........
----keywords: Immortal Life Henrietta Lacks


In a keyword search of tblKeyWordsByIBSN (1 to Many ; IBSN : Keyword in Title)

3456789 Immortal
3456789 Henrietta
3456789 Lacks
3456789 Life

[CJ_London]

Per Alans suggestion, to remove the first noisewords for sorting in a query

SELECT *
FROM tblBooks
ORDER BY Replace(Replace(strConv(left(booktitle,4),3),"The ","",,1,Binary),"A ","",,1,Binary)

Note the space after The and A (otherwise a book called "Theatre Magic" would end up as 'atre Magic')
The 1 after the double comma limits the replace to the first one it finds
the Binary makes it case sensitive - otherwise a book called "Minerva Conquests" would be become "Minerv Conquests" - may not matter for sorting purposes but up to you

the strconv converts your book title to proper case for all words

so this will 'catch' an incorrect case "a town called alice" would otherwise still be sorted on "a town called alice"

However a book called "Winnie The Pooh" will be sorted on "Winnie Pooh". This also can be overcome if required by selecting the first say 4 characters (assuming 'The' is the longest word) and running the routine on that, but to prevent errors you would need to check there are at least 4 characters, then run the routine then add back the remaining characters, something like

ORDER BY Replace(Replace(strConv(iif(len(booktitle)>4,left( booktitle,4),booktitle),3),"The ","",,1,Binary),"A ","",,1,Binary) & iif(len(booktitle)>4,mid(booktitle,5),"")

[orange]

I agree with Ajax and Alan --- I was giving an approach if you have a long list of noise words.
We did a similar thing with company names, product names, services names and had somewhere in the vicinity of 100 noise words Ltd, Limited,Inc, Incorporated, Pty, LLC etc .

It's a common approach to use an "exclusion table" if you have a number of terms, names, people.... that you want to exclude from some process/query.