wildcard

[Guest]

I want to run a query with VBA code that will display all common records from
2 tables. Each table has the column headings of "Name" and "Amount" in
common. I want to display all records that have the exact dollar amount in
the "Amount" column and matches the name with 2 or less wildcard characters
someplace in the name. (my issue is we have many misspelled names in the
table I'm trying to capture). The two tables are titled "Confirm No Match"
and the other titled "Payments No Match".

For example if the name was in the Confirm No Match as "Ouimay" and in the
Payment No Match as "Ouimey" and both dollar amounts were $100.00, the record
would be copied to the "Name Wildcard" query.

I looked at the other posts and in help and it seem that one has to specify
the location of the wildcard, I just want it to be any character or even
better if we could specify to match the name with 2 wildcards or less for
more hits, is that even possible.

 

[John Spencer]

You might be able to use Levenshtein Distance to calculate the match, but it
will be slow for any sizeable number of records.

SELECT *
FROM Table1 INNER JOIN Table2
ON Table1.Amount = Table2.Amount
WHERE LD(Table1.[Name],Table2.[Name]) < 3


Dangers here are that the Amount fields are number fields of type single or
double and may appear to be equal even though there is a small difference
due to the errors that occur with floating point numbers. Also, the
distance for "Aul","Ang" is 2 and these are very probably different people.

You should be able to speed this up a bit by insisting that the first 1 or 2
characters of the name fields match

SELECT *
FROM Table1 INNER JOIN Table2
ON Table1.Amount = Table2.Amount And
Table1.Name Like Left(Table2.Name,2) & "*"
WHERE LD(Table1.[Name],Table2.[Name]) < 3

'Code for module function follows.
See http://www.merriampark.com/ld.htm#VB

'*******************************
'*** 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 s As String, ByVal t As String) As Integer
Dim d() As Integer ' matrix
Dim m As Integer ' length of t
Dim n As Integer ' length of s
Dim i As Integer ' iterates through s
Dim j As Integer ' iterates through t
Dim s_i As String ' ith character of s
Dim t_j As String ' jth character of t
Dim cost As Integer ' cost

' Step 1
n = Len(s)
m = Len(t)
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$(s, i, 1)

' Step 4
For j = 1 To m

t_j = Mid$(t, j, 1)

' Step 5
If s_i = t_j Then
cost = 0
Else
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