FuzzyMatch Function/Logic

[svkroy]

A simple fuzzymatch logic that can be placed in an access module. This is a UDF(User Defined Function), and can be called from a query to retrieve suitable results.

Variations can involve replacing special characters, vowels before matching so as to customise as per requirements.

Function FuzzyMatch_Souvik(ByVal String1 As String, ByVal String2 As String)


Dim intLen1 As Integer, intLen2 As Integer ', matchpercent As Integer


intLen1 = Len(String1)
intLen2 = Len(String2)


'Other amendments
String1 = LCase$(Trim(String1))
String2 = LCase$(Trim(String2))


FuzzyMatch_Souvik = ""


'Extreme Cases
If String1 = String2 Then
    FuzzyMatch_Souvik = 1 ' * 100
    'Exit Function
End If


If intLen1 < 2 Then
    FuzzyMatch_Souvik = 0
    'Exit Function
End If


If Not IsNull(FuzzyMatch_Souvik) Then


'Count spaces in strings
lengthstr1 = Len(String1) - Len(Replace(String1, " ", ""))
lengthstr2 = Len(String2) - Len(Replace(String2, " ", ""))


'Split strings into different words
For i = 0 To lengthstr1
String1Array = Split(String1)
Next i


For i = 0 To lengthstr2
String2Array = Split(String2)
Next i


'Match String1 with String2
For i = LBound(String1Array) To UBound(String1Array)
    dup = False
   For j = LBound(String2Array) To UBound(String2Array)
        If String1Array(i) = String2Array(j) Then
        matchquotient = matchquotient + 1
        matchpercent = matchquotient / (lengthstr1 + 1)
        
            dup = True: Exit For
        End If
    Next j
    If Not dup Then
        remainder = remainder + String1Array(i)
    End If
Next i


'Match String2 with String1
For i = LBound(String2Array) To UBound(String2Array)
    dup = False
   For j = LBound(String1Array) To UBound(String1Array)
        If String2Array(i) = String1Array(j) Then
        matchquotient1 = matchquotient1 + 1
        matchpercent1 = matchquotient1 / (lengthstr2 + 1)
        
            dup = True: Exit For
        End If
    Next j
    If Not dup Then
        remainder = remainder + String2Array(i)
    End If
Next i


End If

FuzzyMatch_Souvik = (matchpercent + matchpercent1) / 2


End Function

 

[jex57]

Thank you for this logic...Not sure if you are able to assist.
I have a single table with my string - how can I modify the code to go to row 1 - run through the rest of row 1 and see if there are matches - write possible matches to a new table, and then move to next record and got to row 2 and see if there is a match