Creating filter in Table

[tinkerbell]

Hi...I want to learn to customize a sorting filter that will:
Ignore the first word in a movie title if the first word is "The", "A", "An", etc.
And have my forms based on the same criteria.

My database contains over 1,000 titles and it would be a beautiful thing if I could sort on the title minus those first words. For instance "The Day of the Jackal" would appear with the D's.....not with a multitude of The's

Can anyone help?

 

[Beetle]

Here is an example of one way to accomplish this;

1) Create a small lookup table that contains the words you want to ignore. Call it tblIgnoredWords with one field named IgnoredWord. Enter the words you want to ignore like "A", "An", "The", etc.

2) Put a function like the following in a public module in your application;

Public Function ShortTitle(strMovieTitle As String) As String
 
'get the first word of the MovieTitle
Dim intFirstSpace As Integer
Dim strFirstWord As String
intFirstSpace = InStr(strMovieTitle, " ") - 1
strFirstWord = Left(strMovieTitle, intFirstSpace)
 
'see if the first word is in the IgnoredWords table
If IsNull(DLookup("IgnoredWord", "tblIgnoredWords", "IgnoredWord=""" _
                  & strFirstWord & """")) Then
    ShortTitle = strMovieTitle
Else
    ShortTitle = Right(strMovieTitle, Len(strMovieTitle) - intFirstSpace - 1)
End If
 
End Function

3) Call the function in a query like the following;

SELECT tblMovies.MovieTitle, ShortTitle([Movietitle]) AS ModifiedTitle
FROM tblMovies
ORDER BY tblMovies.MovieTitle;

You can then sort by the ModifiedTitle field in the query.

 

[tinkerbell]

Thank you SO much for the information!

Hope I'm replying correctly...I've never used a forum before. I'd like to give you a THANKS that would be added to the number of thanks you've had, but can't figure out how to do that.

Here is an example of one way to accomplish this;

1) Create a small lookup table that contains the words you want to ignore. Call it tblIgnoredWords with one field named IgnoredWord. Enter the words you want to ignore like "A", "An", "The", etc.

2) Put a function like the following in a public module in your application;

Public Function ShortTitle(strMovieTitle As String) As String
 
'get the first word of the MovieTitle
Dim intFirstSpace As Integer
Dim strFirstWord As String
intFirstSpace = InStr(strMovieTitle, " ") - 1
strFirstWord = Left(strMovieTitle, intFirstSpace)
 
'see if the first word is in the IgnoredWords table
If IsNull(DLookup("IgnoredWord", "tblIgnoredWords", "IgnoredWord=""" _
                  & strFirstWord & """")) Then
    ShortTitle = strMovieTitle
Else
    ShortTitle = Right(strMovieTitle, Len(strMovieTitle) - intFirstSpace - 1)
End If
 
End Function

3) Call the function in a query like the following;

SELECT tblMovies.MovieTitle, ShortTitle([Movietitle]) AS ModifiedTitle
FROM tblMovies
ORDER BY tblMovies.MovieTitle;

You can then sort by the ModifiedTitle field in the query.

 

[CBrighton]

button on the bottom right of each post, has a thumbs up and says "thanks" on it.

 

[tinkerbell]

Here is an example of one way to accomplish this;

1) Create a small lookup table that contains the words you want to ignore. Call it tblIgnoredWords with one field named IgnoredWord. Enter the words you want to ignore like "A", "An", "The", etc.

2) Put a function like the following in a public module in your application;

Public Function ShortTitle(strMovieTitle As String) As String
 
'get the first word of the MovieTitle
Dim intFirstSpace As Integer
Dim strFirstWord As String
intFirstSpace = InStr(strMovieTitle, " ") - 1
strFirstWord = Left(strMovieTitle, intFirstSpace)
 
'see if the first word is in the IgnoredWords table
If IsNull(DLookup("IgnoredWord", "tblIgnoredWords", "IgnoredWord=""" _
                  & strFirstWord & """")) Then
    ShortTitle = strMovieTitle
Else
    ShortTitle = Right(strMovieTitle, Len(strMovieTitle) - intFirstSpace - 1)
End If
 
End Function

3) Call the function in a query like the following;

SELECT tblMovies.MovieTitle, ShortTitle([Movietitle]) AS ModifiedTitle
FROM tblMovies
ORDER BY tblMovies.MovieTitle;

You can then sort by the ModifiedTitle field in the query.

Thank you...I've used the IIF function to create a new table and works
in the queries for each form I use. Thanks a bunch