Implementing Soundex Search

[Guest]

In MS Access 97, how do I implement a soundex search for a string field which
contains multiple words?

 

[Albert D.Kallal]

In MS Access 97, how do I implement a soundex search for a string field
which
contains multiple words?

Building a soundx search is very easy, and I would recommend for reason of
performance that you duplicate the field you plane to search,a nd save the
soundex code in that field.

However, while building the soundex code search is very easy to do..the 2nd
part of multiple words is a problem.

In fact, lets dump the requirement for soundex, and ask how can you search a
field that has multiple words? (answer = you can't, unless you use a wild
card match..but that means no index will be used..and that is slow).

So, you problem has NOTHING to do with the soundex being easy, or hard (it
is easy to do the sounds part for a field).

So, dumping, and eliminating the soundex part, and simply asking how can you
setup a system to allow searching in a field for multiple keywords is the
problem. If you can solve that easy, then you can easily use the soundx
routine on that code.

However, setting up the searching for multiple keywords in a field...that is
a pain, and a good deal of work if you need indexing.

Anyway, I'll give you the easy part, and here is a soundex routine:

Function strSoundex(strSource As Variant) As Variant

'
' Produces a code based on the "Soundex" method
'
'
' Parms:
' strSource Passed string
'
'
' Written by Albert D. Kallal
'
'
' See page 392 of Knuths' book 'Sorting and Searching', Volume 3 of 'The
' Art of Computer Programming", Addison/Wesley publisher.
'
' Method used:
' 1. Change all lowercase to uppercase
' 2. Retain first letter of input string (must be alpha)
' 3. Ignore the letters A, E, H, I, O, U, W, Y, and any other
' non-alphabetic characters:
' 4. Subsitiute the following
' 1 = B F P V
' 2 = C G J K Q S X Z
' 3 = D T
' 4 = L
' 5 = M N
' 6 = R
' 5. Ignore identical letters next to each other
' 6. Add trailing zeros if the length is less than "max.soundlen"
' characters (in this example it is 4 numeric + 1 alpha = 5).
' Stop when the string reaches max.soundlen (ie:truncate the rest)
'
'START:--------------------------------------------------------------------
'
If IsNull(strSource) = True Then
strSoundex = Null
Exit Function
End If

Const maxsoundlen As Integer = 4 ' Max length of resulting soundex
code
Dim TransTable As String
Dim Offset As Integer ' Offset for easy TABLE translates
Dim SourceLen As Integer
Dim charptr As Integer
Dim testchar As String
Dim lastchar As String
Dim intLookup As Integer
Dim strDigit As String

' Conversion table
' [ABCDEFGHIJKLMNOPQRSTUVWXYZ]
TransTable = "01230120022455012623010202"
Offset = Asc("A") - 1 ' Offset for easy TABLE translates
'
' note: for "AEHIOUWY" are ignored by translating to zero
'
strSource = UCase(strSource) ' convert string to uppercase

SourceLen = Len(strSource) ' find/set string length to process

strSoundex = Left$(strSource, 1) ' Get/set first character
lastchar = strSoundex
charptr = 2 ' We skiped the first char above

Do While (charptr <= SourceLen) And (Len(strSoundex) < maxsoundlen)
'
testchar = Mid$(strSource, charptr, 1) ' get 1 char to test
'
' if different than last character, then process
'
If testchar <> lastchar Then
'
intLookup = Asc(testchar) - Offset
If (intLookup > 0) And (intLookup <= 26) Then
strDigit = Mid$(TransTable, intLookup, 1) ' table translate to
soundex
If strDigit <> "0" Then
strSoundex = strSoundex & strDigit
lastchar = testchar
End If
End If
End If
'
charptr = charptr + 1 ' move on to next character
Loop

'strSoundex = Left(strSoundex & "00000", maxsoundlen) ' pad with
trailing zeros (5 CHARS)

End Function


So, in a last name textbox on a form, you use the "after update" event to
store the soundx in another field (lastnameSDX for example).

Now, you can prompt for a search...convert it to soundex...and then search
the "sdx" field you made....