Ignoring the word "THE" when sorting a report.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am wondering if there is a way, or if someone can hlep me devise a way to
ignore the word "THE" when sorting a report. The field that it is sorting on
is a "Title" filed. The title of the book could be, "The Owners Manual".
When the report is run, that title is placed with the other books that begin
with a "T". I want it to display with the other books that start with an
"O". Of course the solution would be to enter the book in as "Owners Manual,
The". I would like to find a way to avoid that if possible.

Currently using MS Access 2003 but XP is fine if it matters.
 
kknoblauch said:
I am wondering if there is a way, or if someone can hlep me devise a way to
ignore the word "THE" when sorting a report. The field that it is sorting on
is a "Title" filed. The title of the book could be, "The Owners Manual".
When the report is run, that title is placed with the other books that begin
with a "T". I want it to display with the other books that start with an
"O". Of course the solution would be to enter the book in as "Owners Manual,
The". I would like to find a way to avoid that if possible.

Currently using MS Access 2003 but XP is fine if it matters.
Click to expand...


In the Sorting and Grouping window, sort on the expression:

=IIf(Left(title, 4) = "The ", Mid(title, 5), title)
 
I am wondering if there is a way, or if someone can hlep me devise a way to
ignore the word "THE" when sorting a report. The field that it is sorting on
is a "Title" filed. The title of the book could be, "The Owners Manual".
When the report is run, that title is placed with the other books that begin
with a "T". I want it to display with the other books that start with an
"O". Of course the solution would be to enter the book in as "Owners Manual,
The". I would like to find a way to avoid that if possible.

Currently using MS Access 2003 but XP is fine if it matters.
Click to expand...

Easiest is to first create a new column in the query that is the
report's recordsource.
SortColumn:IIf(Left([BookTitle],4) = "The ",Mid([BookTitle],5) & ",
The",[BookTitle])

Then use the SortColumn in the Report's Sorting and Grouping dialog to
sort on.

This will convert a title like "The Longest Day" into "Longest Day,
The"
 
Go back to the query your report is based on. Replace the Title field in
your query with:
BookTitle:IIF(Left([Title],3) = "The",Mid([Title],5),[Title])

Open your report on design view and click on the Sorting and Grouping button
in the toolbar at the top. Where you have Title, use the down arrow and
select BookTitle.

You should now be good to go!

Note: Type the expression into your query very carefully to get the syntax
right!!!
 
Back
Top