Ivan,
Soundex is a Class that I have altered to a public function you can use in
your query. Add the following code into a module and declare it in a query as
per my first reply.
If you use this in the debug window you get the same Soundex code for a
different surname. I hope this helps.
?MakeSoundexA("MacFarlane")
M2164
?MakeSoundexA("McFarlane")
M2164
Option Compare Database
Option Explicit
Public Function MakeSoundexA(From As String) As String
Dim Codes(25) As Byte
Dim PrevCode As Byte
Dim CurrentCode As Byte
Dim i As Integer
Dim j As Integer
Dim CurrentChar As Integer
Dim Soundx As String
Dim A As Byte
Dim Z As Byte
Codes(0) = 0
Codes(1) = 1
Codes(2) = 2
Codes(3) = 3
Codes(4) = 0
Codes(5) = 1
Codes(6) = 2
Codes(7) = 0
Codes(8) = 0
Codes(9) = 2
Codes(10) = 2
Codes(11) = 4
Codes(12) = 5
Codes(13) = 5
Codes(14) = 0
Codes(15) = 1
Codes(16) = 2
Codes(17) = 6
Codes(18) = 2
Codes(19) = 3
Codes(20) = 0
Codes(21) = 1
Codes(22) = 0
Codes(23) = 2
Codes(24) = 0
Codes(25) = 2
If (Len(From) = 0) Then
MakeSoundexA = ""
Exit Function
End If
Z = Asc("Z")
A = Asc("A")
Soundx = UCase$(Left$(From, 1))
PrevCode = Codes(Asc(Left$(Soundx, 1)) - Asc("A"))
i = 2
Do While (i <= Len(From)) And Len(Soundx) < 5
CurrentChar = Asc(UCase$(Mid$(From, i, 1)))
If (CurrentChar >= A And CurrentChar <= Z) Then
CurrentCode = Codes(CurrentChar - A)
If (CurrentCode <> 0) Then
If (CurrentCode <> PrevCode) Then
Soundx = Soundx & Format$(CurrentCode)
End If
End If
PrevCode = CurrentCode
End If
i = i + 1
Loop
Soundx = Left$(Soundx & "00000", 5)
MakeSoundexA = Soundx
End Function
