Sort alphabetically and ignore common words like 'A', 'An' & 'The'

Firstly you may find it easier to have e field the user enters as the Sort Field so they enter "The Company Name" in the CompanyName field and "Company Name (The)" in the CompanyNameSort field.

Otherwise I think you will need to

1. Create a table to contain all the words you want to ignore
2. Create a function that chacks the table and eliminates all the words in it.
3. Create a query that calls the function passing the Company Name field and sort its results.

If you are using a large number of records the function will be slower

Cheers, Andrew

Hey troycox!

  You will need a function to accomplish this.

  Public Function SortThis(sSortOn) as String
   
    dim IgnoreThese as Variant, ix as integer
    dim tmpSortkey as String

    IgnoreThese = Array("A ","An ", "The ", " a ", " an ", " and ")
    for ix = 0 to ubound(IgnoreThese)
      tmpSortKey = replace(sSortOn,IgnoreThes(ix),"")
    next ix
  End Function

  The function replaces the elements of the array when found with a zero len string.  The remaining portion of the string can be sorted without the undesired parts.

regards
Jack

Oh I forgot to mention that if you get the funcvtion to work then you could use it to populate the CompanynameSort field automatically then your query will be able to use an index and be more efficient.

Cheers, Andrew

you can add elements to the array as you think of them...  or like Andrew says, tablize them and draw them from there.  (a dictionary table)

Jack the problem with your code is that you would be reenaming jadedata to jadedat

Also are you sure that spaces are the only word seperation character you need to handle.

Cheers, Andrew

hmmmm...

Jack, this was why i didn't even try to do the code it raises more questions than answers.

BTW this is what you can do with searches under Internet Information Services, it could be that 2003 has something built in. The words like The An are refered to a NOISE words (I think)

Cheers, Andrew

Hi guys,

I did a search in Access 2003 help and couldn't find any built-in feature that could ignore 'noise words'. Looks like I'll have to find a custom solution.

I have pasted jadedata's function into a common module and then used it in a query as follows:

SELECT tblReferrers.[Referrer ID], tblReferrers.[Referrer Description], SortThis([Referrer Description]) AS SortName
FROM tblReferrers
ORDER BY SortThis([Referrer Description]);

Strangely, it seems to do nothing. In other words, the result set is not sorted in any way and I get no errors. In fact, the SortThis column is blank.

Where am I going wrong?

Also, I only need to ignore words at the beginning of the name so maybe the function can updated to only process the first word??

For example:

I have in my list:

Homeside
Realty One
The Arthichoke Group
The Investment Network

It should sort as:

The Artichoke Group
Homeside
The Investment Network
Realty One

Regards,
troycox

Hey TextReport,

That worked perfectly!!! And I agree with you, MS should add this as a built-in feature of Access.

I love this service!

Regards,
troycox

Jack in recognision of your part in starting this function I have created a points for question.

https://www.experts-exchange.com/questions/20812138/Points-for-jadedata-for-Q-20812097.html

Cheers, Andrew