Soundex

[lewis33]

I understand there is a Soundex and also a Difference function available in SQL Server. I am not too knowledgeable with VB but here is my dilemma. I would like to compare two tables that have first names and last names and I think the Soundex function may be of help. I've read about the Difference function that assigns values from 1-4 and that sounds helpful too. My problem is that I only have MS Access 2000 at work and none of this readily available. Does anyone know if there is any (hopefully) easy to use modules that approximates the Soundex funtion for my level of Access version and Access skill?

agradece a una tonelada - Dan

 

[traingamer]

There are many routines out there including:

http://msdn2.microsoft.com/en-us/library/aa662178(office.11).aspx

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

Or this one that i've used (I can't remember where it's from):

Public Function SoundsLike(ByVal pWord As String, Optional pAccuracy As Byte = 4) As String
  On Error GoTo LocalError
  '  char importance "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
  Dim x As Integer
  Dim CChar As String

  If pAccuracy > 10 Then
    pAccuracy = 10                     ' maximum accuracy allowed
  ElseIf pAccuracy < 4 Then
    pAccuracy = 4                      ' minimum accuracy allowed
  End If
  ' account for the first character
  pAccuracy = pAccuracy - 1

  pWord = UCase(pWord)

  ' strip all invalid characters
  For x = 1 To Len(pWord)
    If Asc(Mid(pWord, x, 1)) < 65 Or _
        Asc(Mid(pWord, x, 1)) > 90 Then
      Mid(pWord, x, 1) = "@"           ' assign a catchable value
    End If
  Next x
  pWord = Trim(pWord)

  SoundsLike = pWord
  ' assign values to the string
  SoundsLike = Replace(SoundsLike, "A", "0")
  SoundsLike = Replace(SoundsLike, "E", "0")
  SoundsLike = Replace(SoundsLike, "I", "0")
  SoundsLike = Replace(SoundsLike, "O", "0")
  SoundsLike = Replace(SoundsLike, "U", "0")
  SoundsLike = Replace(SoundsLike, "Y", "0")
  SoundsLike = Replace(SoundsLike, "H", "0")
  SoundsLike = Replace(SoundsLike, "W", "0")
  SoundsLike = Replace(SoundsLike, "B", "1")
  SoundsLike = Replace(SoundsLike, "P", "1")
  SoundsLike = Replace(SoundsLike, "F", "1")
  SoundsLike = Replace(SoundsLike, "V", "1")
  SoundsLike = Replace(SoundsLike, "C", "2")
  SoundsLike = Replace(SoundsLike, "S", "2")
  SoundsLike = Replace(SoundsLike, "G", "2")
  SoundsLike = Replace(SoundsLike, "J", "2")
  SoundsLike = Replace(SoundsLike, "K", "2")
  SoundsLike = Replace(SoundsLike, "Q", "2")
  SoundsLike = Replace(SoundsLike, "X", "2")
  SoundsLike = Replace(SoundsLike, "Z", "2")
  SoundsLike = Replace(SoundsLike, "D", "3")
  SoundsLike = Replace(SoundsLike, "T", "3")
  SoundsLike = Replace(SoundsLike, "L", "4")
  SoundsLike = Replace(SoundsLike, "M", "5")
  SoundsLike = Replace(SoundsLike, "N", "5")
  SoundsLike = Replace(SoundsLike, "R", "6")

  CChar = Left(SoundsLike, 1)
  For x = 2 To Len(SoundsLike)
    If Mid(SoundsLike, x, 1) = CChar Then
      Mid(SoundsLike, x, 1) = "@"
    Else
      CChar = Mid(SoundsLike, x, 1)
    End If
  Next x
  SoundsLike = Replace(SoundsLike, "@", "")

  SoundsLike = Mid(SoundsLike, 2)
  SoundsLike = Replace(SoundsLike, "0", "")

  SoundsLike = SoundsLike & String(pAccuracy, "0")
  SoundsLike = Left(pWord, 1) & Left(SoundsLike, pAccuracy)
  Exit Function
LocalError:
End Function

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

Looks like that was a variation on

http://www.freevbcode.com/ShowCode.Asp?ID=147

(I'll stop now)

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[lewis33]

Thanks, that sounds kind of like the difference function.
Sorry to sound dense but I have an access name called T_NAMES with fields first and last and a second table called T_NAMES2 with same fields. Can I use this code to compare the two?