Removing "The" from a list.

  • Thread starter Thread starter Frank Martin
  • Start date Start date
F

Frank Martin

I use Access2003 for a small database to record my movie
collection.

When I use combos to look up records, I want to suppress the
word "the" when searching for a movie in a query.

Is there some way to adjust the query field to do this.

Please help, Frank.
 
SELECT *
FROM TableName
WHERE Replace([MovieNameField], "The ", "") = "SearchStringValue";
 
I use Access2003 for a small database to record my movie
collection.

When I use combos to look up records, I want to suppress the
word "the" when searching for a movie in a query.

Is there some way to adjust the query field to do this.

Please help, Frank.
Click to expand...

Instead of using the title by itself as a column, create a calculated
field:
NewTitle:IIf(Left([Title],4) = "The ",Mid([Title],5) & ", The",
[Title])

The above will either display the Title, or if the first word is "The
" move the "The " to the end of the title,
i.e. "Producers, The"
 
It won't be just THE. You will also want to omit other leading words such as
A and AN. And there may be other variations as well: for example, it is
common to sort names starting with MAC and MC together.

Best solution will be to add another field to your table, called (say)
SortTitleAs. Use an Update query to populate it with your existing titles.
Then remove the THE, A, AN, etc from the relevant records, and sort on this
field.

You can write some code in the AfterUpdate event procedure of the movie
title field to copy the same value to the SortTitleAs field, omitting the
leading bad words. The main advantage is that you can specify how anything
should be sorted, even titles like "I heart Huckabbess."

Be sure to index the SortTitleAs field for fast indexing.
 
Actually, my suggestion is a bit severe. It'll remove all occurrences of
"the" within the entire name.

SELECT *
FROM TableName
WHERE Replace(Left([MovieNameField], 4), "The ", "") &
Mid([MovieNameField], 4) = "SearchStringValue";

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Ken Snell (MVP) said:
SELECT *
FROM TableName
WHERE Replace([MovieNameField], "The ", "") = "SearchStringValue";

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Frank Martin said:
I use Access2003 for a small database to record my movie collection.

When I use combos to look up records, I want to suppress the word "the"
when searching for a movie in a query.

Is there some way to adjust the query field to do this.

Please help, Frank.
Click to expand...
Click to expand...
 
fredg said:
I use Access2003 for a small database to record my movie
collection.

When I use combos to look up records, I want to suppress the
word "the" when searching for a movie in a query.

Is there some way to adjust the query field to do this.

Please help, Frank.
Click to expand...

Instead of using the title by itself as a column, create a calculated
field:
NewTitle:IIf(Left([Title],4) = "The ",Mid([Title],5) & ", The",
[Title])

The above will either display the Title, or if the first word is "The
" move the "The " to the end of the title,
i.e. "Producers, The"
Click to expand...

I wonder if the original poster could clarify what he's trying to do. It
seems obvious from the various replies that people are interpreting the
post to mean that he wants to remove the word "the" from the beginning
of a movie title, but when re-reading the original post, all I see is
that he wants to suppress the word "the". It doesn't specify from where,
which implies that he wants to suppress all occurrences of the word "the".

The way I interpret the question is, if he typed "the producers" into a
search field, then the query would actually search for "producers". Or,
if he typed "lord of the rings" into the search field, it would search
for "lord of rings".
Click to expand...

It's sometimes a guessing game as to what a poster really wants. <g>

I took it to mean he wanted to type "Producers" in the combo and
return the movie "The Producers", rather than having to type the
leading word The in the full title.
 
WHERE SavedTitle LIKE "*" & MovieTitleYouLookFor


but it won't only match a missing The. In fact, it will match anything movie
title ending with what is in MovieTitleYouLookFor. Alternatively, if your
parameter will have the starting THE and the save title won't, then


WHERE MovieTitleYouLookFor LIKE "*" & SavedTitle


is more appropriate (but again, any saved title ending by what you typed
will be a match).



Vanderghast, Access MVP
 
Back
Top