[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2003-11-30
11
Medium Priority
?
666 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 2000 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

656 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