Solved

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

Posted on 2003-11-30
11
658 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
  • 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
 
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

911 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now