Whats a good way to appraoch common business names that start the same 2

[WallT]

For instance...

Law offices of...etc
Dr. so and so

How would you approach sorting for search tables and combo boxes etc...

Just a programming problem that I am sure is quite normal. Thanks for any articles you may be able to point me at on this topic.

 

[Darrylles]

Hi,

Can you explain your requirements please?

I've a feeling that you are completely out of your depth, but please try.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[WallT]

It's a customers database for the legal community and many, many lawfirms names begin with "Law Offices of." So try to locate them by sorting or using a query by name will bring up thousands oflaw firms that begin this way.

I am assuming that I am probably going to have to use some type of len() code to eleminate "law office of," for searching but I don't have the experiance without some help or a better idea.

 

[Remou]

You can use Like:

[tt]Like "*Blah*"[/tt]

Which will return:

Blah, Blah & Blah Solicitors
Law Offices of Blah

I have also seen fields that store initials:
BBBS
LOB1

 

[MajP]

If you had a couple of common terms at the beginning that you would like to strip off you could build a function something like:

Public Function removeCommon(strName As String)
  
  If InStr(strName, "Law Office of ") = 1 Then
    strName = Replace(strName, "Law Office of ", "")
  End If
  
  If InStr(strName, "The Law Offices of ") = 1 Then
    strName = Replace(strName, "The Law Offices of ", "")
  End If
  
  If InStr(strName, "The Firm of ") = 1 Then
    strName = Replace(strName, "The Firm of ", "")
  End If
  
  If InStr(strName, "Law Firm of ") = 1 Then
    strName = Replace(strName, "Law Firm of ", "")
  End If
  
  removeCommon = strName

End Function

You can add as many common terms as you wish.

Then in a query you could use the function on a calculated field. Something like

SELECT tblFirms.strName, removeCommon([strName]) AS shortName
FROM tblFirms
ORDER BY removeCommon([strName]);

 

[TheAceMan1]

How are WallT . . .

I agree with [blue]Remou![/blue] The [blue]Like[/blue] operator allows you to search for more specific info other than that which is common. Your real problem is [blue]extracting the info to search for![/blue]

A starting point:

[blue]   Dim strSearch As String

   strSearch = Mid("FieldName",16,5)[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[WallT]

I'm not quite sure I understand how to use that code AceMan. This database, which I inherited, currently uses a query to open a form with criteria in the Firm Name as follows:

Like [Enter one or more letters of the Firm name] & "*"

But as I have found, when you type "LAW" or "L" for instance, you would get over a thousand firms when the form opens.

 

[TheAceMan1]

WallT . . .

[blue]Mid("FieldName",16,5)[/blue]

Returns the 1st five characters after the 16th character bypassing [blue]Law Offices of[/blue]. You can of course play with this.

Giving:

[blue]Like "*" & Mid("FieldName",16,5) & "*"[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Remou]

I have a database of journals, a large number of these begin "Journal of ". To return results for Journals with a particular word or phase in the title, I would have the user complete a textbox, then create an SQL string and use it as the RecordSource for a form:

strSQL="Select JournalTitle From tblJournals " _
& "Where JournalTitle Like '*" & txtTextbox & "*'"
Me.RecordSource=strSQL

 

[JoeAtWork]

I think I would have something like the removeCommon function that MajP showed in his post, but I would not use it in the search query as functions will slow done the query to a great degree.

Instead, I would have a ShortName field in the table. This is a field that the user doesn't need to know exists. On the form where you add or edit records, in the AfterUpdate event of the Name event I would do:

ShortName = removeCommon(Name)

Then when doing searches I would search against ShortName instead of Name.

 

[Remou]

One problem with a short name field is maintaining it. If data-entry people mis-spell the full name or omit one of the common words, automation will fail to strip the common phrase and the short name will end up having the same contents as the full name. If data-entry includes the short name, it is not unlikely that two different versions will be entered. In addition, I imagine that there are a number of common phrases that can occur both before and after the relevant part of the name. In some cases, the common phrase may be important in distinguishing between two firms with the same core name but different common phrases.

I cannot imagine that law firms run to millions, so I do not think that a search using Like will run into time problems.

 

[MajP]

I agree that using the Like operator and some type of search form would be the way to go to search for a name. However I interpreted the original post to say that they wanted to sort a list based on the "base name".

How would you approach sorting for search tables and combo boxes etc.

Often you will see a long list with names that start with "The", "An", "A" etc not included in the sort order. So "The Beatles" would appear under "B" not "T".

So I proposed this for sorting not searching. Also I agree with JoeAtWork that with thousands of firms this would be slow. I would use the function in an update query to populate a shortName field, then after that begin to maintain it.

 

[Remou]

It seems to me that to sort by anything but the full name can only lead to problems as the logic must then be explained to each new person. A, An and The are know to be placed at the end or removed, though less often than formerly: 'The Who', for example, is rarely shown as 'Who, The'. It can be useful to use Autokeys or Autocorrect to allow the user to enter initials that will be replaced: 'lo' could represent 'Law Offices of'. The user can be shown how to create these abbreviations, which will cut down on changes to coding when a new common phrase is recognized.

 

[MajP]

Remou,
I was thinking that they wanted something like this where you sort by primary info, but display the long name:

http://www.poprockbands.com/

In this logic "The Who" is shown as "The Who", but appears under W not T. I think this is common logic, my MP3 player does it as well.

 

[JoeAtWork]

It seems to me that to sort by anything but the full name can only lead to problems as the logic must then be explained to each new person.

Seems to contradict with

The user can be shown how to create these abbreviations

And with my ShortName solution the user doesn't need to have anything explained. As I said it would be updated automatically. It doesn't take much code to ensure that ShortName is always updated when the full name is. I have done similar when making SOUNDEX indexes on certain fields. I certainly wouldn't "explain" to a user the SOUNDEX algorithm and have him enter it himself. Just design the application well so that the data entry form is the only place a user can update the table.

If data-entry people mis-spell the full name or omit one of the common words, automation will fail to strip the common phrase

The same problem occurs if you strip the common phrase directly in the query, so I don't see how that is an argument against my method. The only difference is my query will run faster.

 

[Remou]

Yes, it does sound contradictory. Stripped fields may be unfamiliar to the user as they have not been created by him or her, whereas it is not unlikely that the user is already aware of the Autocomplete function and can be shown how to create these abbreviations, if necessary. Any abbreviations created by the user will be in keeping with his or her way of thinking. Furthermore, if an abbreviation is forgotten, it can be looked up.

I was not suggesting that the common phrase should be stripped in the query, only that records should be returned that include a keyword in any position. It is possible to use such a query with combo boxes.

I realize that such a method could cause a query to run more slowly, however, as I mentioned, I do not think that there could be so many law companies that speed would be an issue.

 

[Darrylles]

Hi,

The idea of stripping words stored in a table sounds optimal to me. String manipulation is notoriously time-consuming.

The alternative is admin-based functionality that 'categorises' law firm names which will take more time.

ATB

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience.