Filtering "A" An" "The"

[Power Post 2000]

Wondering if it was possible on forms/reports to take a title of a
book or movie or a CD (in my case) and sort it correctly by taking out
the leading words A, An, The etc... For example, when I open my
database and looking through it for a particular CD, I would like to
be able to view The Addams Family before Star Wars. Thanks for any
info on the accomplishment of this sorting technique.

 

[Brendan Reynolds]

It can be done with some code ...

Public Function TrimArticle(ByVal strInput As String) As String

Select Case True
Case UCase$(Left$(strInput, 2)) = "A "
TrimArticle = Mid$(strInput, 3)
Case UCase$(Left$(strInput, 3)) = "AN "
TrimArticle = Mid$(strInput, 4)
Case UCase$(Left$(strInput, 4)) = "THE "
TrimArticle = Mid$(strInput, 5)
Case Else
TrimArticle = strInput
End Select

End Function

.... and a query ...

SELECT TrimArticle([TestText]) AS Trimmed
FROM tblTest
ORDER BY TrimArticle([TestText]);

Sorting on a calculated column like this is not very efficient. It would be
more efficient to enter the data as, for example, "Addams Family, The", and
index the column. Or, if you prefer, use two columns, one for the article
and one for the rest of the title. That way, you can concatenate them back
together again for display purposes, for example, in a query:
FullTitle:[Article] & " " & [Title]. If this is a personal collection, the
difference may not be noticeable. If you were managing the inventory of a
library or store, though, it could be significant.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Andi Mayer]

Wondering if it was possible on forms/reports to take a title of a
book or movie or a CD (in my case) and sort it correctly by taking out
the leading words A, An, The etc... For example, when I open my
database and looking through it for a particular CD, I would like to
be able to view The Addams Family before Star Wars. Thanks for any
info on the accomplishment of this sorting technique.

 

[Andi Mayer]

I do it a little bit different (because I use more Languages , but
most of them use up the 3 letters for Artikel)

Public Function TrimArticle(ByVal strInput As String) As String
dim Pos as long
Pos=instr(1,strinput,chr(32))
if pos<=4 then
TrimArticle=mid(stInput,pos+1)
else
TrimArticle=stInput
endif
End Function

I do this one time with a table-Query to isolete the Artikels.
Sometimes I need more runs to get everything with for eaxmple the
German word "Eine Geschichte ...." (a (single) story....)

 

[FatMax]

This expression in a query seems to work OK
Strip:IIf([title] Like "The*",Mid([title],5,46),[title])

Assuming field containing film titles is called title and field length
is 50.For the example you've given
Split: IIf([title] Like "The*",Mid([title],5,46),IIf([title] Like
"An*",Mid([title],4,46),IIf([title] Like "A
*",Mid([title],3,46),[title])))

Probably not a very efficient way of doing things but HTH

FatMax

 

[John Vinson]

This expression in a query seems to work OK
Strip:IIf([title] Like "The*",Mid([title],5,46),[title])

Assuming field containing film titles is called title and field length
is 50.For the example you've given
Split: IIf([title] Like "The*",Mid([title],5,46),IIf([title] Like
"An*",Mid([title],4,46),IIf([title] Like "A
*",Mid([title],3,46),[title])))

You will want to put a blank before the asterisk: this code, as
written, will convert a title "Theseus" into "seus" and "Andromeda
Strain" into "dromeda strain".


John W. Vinson[MVP]

 

[Brendan Reynolds]

Wouldn't do for English, Andi, where we have many more three-letter words
....

? trimarticle("Car Wash")
Wash

? trimarticle("Cop Story")
Story

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Andi Mayer]

Wouldn't do for English, Andi, where we have many more three-letter words
...

? trimarticle("Car Wash")
Wash

? trimarticle("Cop Story")
Story

That't the reason I wrote:
Sometimes I need more runs to get everything ....

Actually I never worked with book titels.
And the word everthing is also wrong, if the false positives reaches a
surtain level you have to stop and adjust

 

[Mike Painter]

I missed the start of this but the most common way to filter such words is
with a "stop list"
This is just a table with one word you want left out per record and you
check against that.

Fast and fairly easy to do with VB's split and replace.