Re: Testing for names that sounds alike

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

No SOUNDEX is designed for matching names and even there it is not highly accurate.

Matching something like that is going to be VERY difficult. I would look at creating a custom vba function to try to regularize the names.

-- replace underscores with spaces
-- break the name down into pieces and keep the first 4 char of each word.
-- Keep the extension

Something like the following UNTESTED function

Public Function regularName(strIN, Optional iLen As Long = 4) As String
Dim StrOut As String
Dim vWords As Variant
Dim I As Long

If Len(Trim(strIN & "")) = 0 Then
regularName = strIN
Else

strIN = Replace(strIN, "_", " ")
vWords = Split(strIN, " ")
For I = 0 To UBound(vWords)
If Len(vWords(I)) > 0 Then
If InStr(vWords(I), ".") > 0 Then
StrOut = StrOut & " " & _
Left(Left(vWords(I), Len(vWords(I)) - 4), iLen) & _
Right(strIN, 4)
Else
StrOut = StrOut & " " & Left(vWords(I), iLen)
End If
End If
Next I
regularName = Mid(StrOut, 2)

End If

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Danny J. Lesandrini wrote:
John:

It seems to me that the SoundEx function worked on names
and could say that Smyth is a lot like Smith. Can it, or your
function tell me that these two strings are alike?

ONC123 prot assign.pdf
ONC123_Protocol_Assignment.pdf

It's probably obvious what I am doing here. Got a directory
full of files that don't follow naming conventions. They are all
close, but spaces where underscores belong and abbrevs.

Assuming I replaced underscores with spaces before I did
the compare (or perhaps not), would your fSoundex function
work on this? If not, something else? Fuzzy compare Fn?

.

Relevant Pages

  • Re: Use of Soundex fields in access
    ... More sound variations (10 basic codes ... An aphabetic string, ... ' The following web-based D-M calculators to test SoundEx results, ... Dim intEncodeStrLen As Integer ...
    (comp.databases.ms-access)
  • Re: Help needed implementing fuzzy logic
    ... Function Soundex(Name As String) As String ... ' Implements SOUNDEX algorithm, reasonably efficiently. ... Dim SoundTemp As String ... NameTemp = UCase)) ...
    (microsoft.public.access.modulesdaovba)
  • Re: Use of Soundex fields in access
    ... Public Function SOUNDEX(strToEncode As String) As String ... ' Usage: SoundEx = SoundEx ... Dim intEncodeStrLen As Integer ... Dim strLastCode As String ...
    (comp.databases.ms-access)
  • Re: Soundex Function
    ... Function Soundex (ByVal S As String) As String ... Dim Code As Integer: Code = 0 ... > I'd like to know if there is something similar to Sql Server function> soundexin Access? ...
    (microsoft.public.access.queries)
  • Re: function to strip out matching value? vb Noob
    ... I want to write a VB function that when passed a matching left side ... I would create a regular expression from the identifier. ... Dim identifier As String = "mary" ...
    (microsoft.public.dotnet.languages.vb)