Solved

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

Posted on 2003-11-30
11
660 Views
Last Modified: 2011-09-20
Hi Experts,

I'm using Access 2003 and am sorting a list of company names alphabetically. I'd like Access to ignore common words such as 'A', 'An' & 'The'.

Does anyone know how to do this in an Access query?

Regards,
troycox
0
Comment
Question by:troycox
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
11 Comments
 
LVL 28

Expert Comment

by:TextReport
ID: 9846508
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
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9846510
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
0
 
LVL 28

Expert Comment

by:TextReport
ID: 9846519
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
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 32

Expert Comment

by:jadedata
ID: 9846527
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)
0
 
LVL 28

Expert Comment

by:TextReport
ID: 9846533
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
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9846545
hmmmm...
0
 
LVL 28

Expert Comment

by:TextReport
ID: 9846557
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
0
 

Author Comment

by:troycox
ID: 9846616
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
0
 
LVL 28

Accepted Solution

by:
TextReport earned 500 total points
ID: 9846621
Jack to expand on your function ( and to debug the error as you keep resetting the tmpSortKey so it only returns the last one from the array)

Cheers, Andrew

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")
    tmpSortkey = sSortOn
    For ix = 0 To UBound(IgnoreThese)
      tmpSortkey = Replace(tmpSortkey, " " & IgnoreThese(ix) & " ", " ")
      tmpSortkey = StripMultipleSpaces(tmpSortkey)
     
      If Left(tmpSortkey, Len(IgnoreThese(ix)) + 1) = IgnoreThese(ix) & " " Then
         tmpSortkey = Mid(tmpSortkey, Len(IgnoreThese(ix)) + 1)
         tmpSortkey = StripMultipleSpaces(tmpSortkey)
      End If
     
      If Right(tmpSortkey, Len(IgnoreThese(ix)) + 1) = " " & IgnoreThese(ix) Then
         tmpSortkey = Left(tmpSortkey, Len(tmpSortkey) - Len(IgnoreThese(ix)) + 1)
         tmpSortkey = StripMultipleSpaces(tmpSortkey)
      End If
     
    Next ix
   
    SortThis = (tmpSortkey)
   
End Function

Function StripMultipleSpaces(pstrString As String) As String
Dim cnt As Long
Dim strString As String

   strString = Trim(pstrString)
   Do Until InStr(strString, "  ") = 0
      strString = Left(strString, InStr(strString, "  ") - 1) & " " & Trim(Mid(strString, InStr(strString, "  ")))
   Loop
   
   StripMultipleSpaces = strString
   
End Function

0
 

Author Comment

by:troycox
ID: 9846763
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
0
 
LVL 28

Expert Comment

by:TextReport
ID: 9846921
Jack in recognision of your part in starting this function I have created a points for question.

http://www.experts-exchange.com/Databases/MS_Access/Q_20812138.html

Cheers, Andrew
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question