Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2007


    I am trying to use the soundex and difference functions in MS ACCESS. I found the code for the soundex function, but I cannot find the code for the difference function. Can anyone help?

  2. #2
    Join Date
    Nov 2003
    I was stumbling through some older posts and come across yours.
    First off....Welcome to dbforums

    Secondly...Sorry for the late response.

    Just a thought here....(and this is the way I would go)... how about considering the implimentation of the Levenshtein String Distance Algorithm. In my opinion, I think the Difference function utilizes this algorithym anyways but more so in a limited fashion.

    The Levenshtein Distance (or edit distance) is a measure of the similarity between two strings. The distance is the number of deletions, insertions, or substitutions required to transform String1 into String2. For example:

    If String1 is "Test" and String2 is "Test", then LD(String1,String2) = 0, because no transformations are needed. The strings are already identical.

    If String1 is "Test" and String2 is "Tent", then LD(String1,String2) = 1, because one substitution (change "s" to "n") is sufficient to transform String1 into String2. The greater the Levenshtein Distance, the more difference there is between the two Strings.

    The Difference function provides a comaprison difference from 0 to 4 with 0 considered to be the closest match a 4 considered to be the least likely match. With the Levenshtein Distance you can go even deeper with 0 still considered as the closest or exact match. A little more flexability I guess.

    For Example, with the Function below which was originally created by Michael Gilleland, if you supply:

    intRet = LD("dbforums", "dbforums")

    intRet will be 0 becuase it is an exact match. If you supply:

    intRet = LD("dbforums", "dbForums")

    intRet will be 1. The LD function is case sensitive. If you don't care for that much accuracy then convert both strings to either all lower or upper case when supplying them to the LD function:

    intRet = LD(UCase("dbforums"), UCase("dbForums"))

    intRet will be 0

    To see a greater distance value supply:

    intRet = LD("dbforums", "This is far from that")

    intRet will be 18

    Here is the LD Function code. It is also comes with another required function named Minimum which the LD function accesses:

    '*** Get minimum of three values
    Private Function Minimum(ByVal a As Integer, _
                             ByVal b As Integer, _
                             ByVal c As Integer) As Integer
      Dim mi As Integer
      mi = a
      If b < mi Then
        mi = b
      End If
      If c < mi Then
        mi = c
      End If
      Minimum = mi
    End Function
    '*** Compute Levenshtein Distance
    Public Function LD(ByVal StringA As String, ByVal StringB As String) As Integer
       Dim d() As Integer ' matrix
       Dim m As Integer ' length of StringB
       Dim n As Integer ' length of StringA
       Dim i As Integer ' iterates through StringA
       Dim j As Integer ' iterates through StringB
       Dim s_i As String ' ith character of StringA
       Dim t_j As String ' jth character of StringB
       Dim cost As Integer ' cost
      ' Step 1
      n = Len(StringA)
      m = Len(StringB)
      If n = 0 Then
        LD = m
        Exit Function
      End If 
      If m = 0 Then
        LD = n
        Exit Function
      End If 
      ReDim d(0 To n, 0 To m) As Integer
      ' Step 2
      For i = 0 To n
        d(i, 0) = i
      Next i
      For j = 0 To m
        d(0, j) = j
      Next j
      ' Step 3
      For i = 1 To n
         s_i = Mid$(StringA, i, 1)
         ' Step 4
         For j = 1 To m
          t_j = Mid$(StringB, j, 1)
          ' Step 5
          If s_i = t_j Then
            cost = 0
            cost = 1
          End If
          ' Step 6
          d(i, j) = Minimum(d(i - 1, j) + 1, d(i, j - 1) + 1, d(i - 1, j - 1) + cost)
        Next j
      Next i
      ' Step 7
       LD = d(n, m)
      Erase d
    End Function
    What the the LD function "Difference" if you like.

    I use both the SoundEx and LD function all the time for enhanced search operations on specific fields among other things. When I get time I'll submit a sample Search Form utilizing these functions among several other things.

    Self Taught In ALL Environments.....And It Shows!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts