Sorting Company Names, Excluding "The"

  • Thread starter Thread starter vsingler
  • Start date Start date
V

vsingler

We have a Access 2000 database of companies and want to
sort the company names in alphabetical order. Some of the
names start with "The", but we want the query to skip
over "The" when it performs the sort. For example, we
would want Boeing and The Boys And Girls Club to look like
this --

Boeing Corporation
The Boys And Girls Club

-- instead of having The Boys And Girls Club fall under
the T's.

How would we go about this?

Thank you in advance for any help you can provide.
 
Hi,

You need to create a field that contains the name in the form in which
it should be sorted. Often it's best to do this with a calculated field
in a query. For instance, something like this will drop the "The":

IndexName: IIf(Left([CompanyName],4)="The ", Mid([CompanyName], 5),
[CompanyName])

and more complex expressions can be used to drop "A ", "El " and so on.

If there are a large number of records this may work unacceptably
slowly. In that case, add a field to the table to store the IndexName,
and take steps in the user interface to ensure that it's automatically
kept up to date when records are added or edited.
 
In the Query, you can create a Calculated Field that removes the "The" and
then sort by this calculated Field. The SQL String should be something
like:

SELECT Customers.CustomerID, Customers.CompanyName
FROM Customers
ORDER BY
IIf(Left([CompanyName],3)="The",Mid([CompanyName],5),[CompanyName]);
 
Thanks for your help . . . we're going to give this a try
and see how it works. I'll post back with the results.

Val
-----Original Message-----
Hi,

You need to create a field that contains the name in the form in which
it should be sorted. Often it's best to do this with a calculated field
in a query. For instance, something like this will drop the "The":

IndexName: IIf(Left([CompanyName],4)="The ", Mid ([CompanyName], 5),
[CompanyName])

and more complex expressions can be used to drop "A ", "El " and so on.

If there are a large number of records this may work unacceptably
slowly. In that case, add a field to the table to store the IndexName,
and take steps in the user interface to ensure that it's automatically
kept up to date when records are added or edited.

We have a Access 2000 database of companies and want to
sort the company names in alphabetical order. Some of the
names start with "The", but we want the query to skip
over "The" when it performs the sort. For example, we
would want Boeing and The Boys And Girls Club to look like
this --

Boeing Corporation
The Boys And Girls Club

-- instead of having The Boys And Girls Club fall under
the T's.

How would we go about this?

Thank you in advance for any help you can provide.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Back
Top