Sounds Like Search

22 views
Skip to first unread message

Hannah Melton

unread,
Jul 8, 1999, 3:00:00 AM7/8/99
to
I have a form which asks for an address by parts, i.e. street number,
street direction, street name and street type. I also have a table of
all the street names in the city. I would like to check the user's
input against this file and if the street name doesn't exist in the
table of accepted street names, pop up some possible matches for the
user to choose from. The possible matches being names that sound like
what the user typed in. I can't find anything like this in Access. Does
anyone have any ideas? Thanks in advance!

HM Melton


Lyle Fairfield

unread,
Jul 8, 1999, 3:00:00 AM7/8/99
to
Try a Soundex Function. Here are a couple. I think the second is Joe
Foster's????

Joe's is much faster on the first call. But if you give aChar a module wide
scope with a separate declaration and sub, I believe it will be faster for
multiple calls.

Lyle

Option Compare Database
Option Explicit

Sub test()
Debug.Print Soundex("Euler")
'E460
Debug.Print Soundex("Gauss")
'G200
Debug.Print Soundex("Hilbert")
'H416
Debug.Print Soundex("Knuth")
'K530
Debug.Print Soundex("Lloyd")
'L300
Debug.Print Soundex("Lukasiewicz")
'L222
End Sub

Public Function CSSoundex(ByVal rString As String) As String

Dim aChar(0 To 255) As String, varElement As Variant
Dim aString() As Byte, varByte As Variant, varPreviousByte As Byte,
booIsFirst As Boolean


For Each varElement In aChar
varElement = ""
Next varElement

aChar(66) = "1" 'B
aChar(70) = "1" 'F
aChar(80) = "1" 'P
aChar(86) = "1" 'V

aChar(67) = "2" 'C
aChar(71) = "2" 'G
aChar(74) = "2" 'J
aChar(75) = "2" 'K
aChar(81) = "2" 'Q
aChar(83) = "2" 'S
aChar(88) = "2" 'X
aChar(90) = "2" 'Z

aChar(68) = "3" 'D
aChar(84) = "3" 'T

aChar(76) = "4" 'L

aChar(77) = "5" 'M
aChar(78) = "5" 'M

aChar(82) = "6" 'R

rString = StrConv(rString, vbUpperCase)
aString = rString
booIsFirst = True

For Each varByte In aString
If booIsFirst Then
CSSoundex = Chr(varByte)
booIsFirst = False
varPreviousByte = varByte
ElseIf aChar(varByte) <> "" Then
If varByte <> varPreviousByte Then
CSSoundex = CSSoundex & aChar(varByte)
If Len(CSSoundex) = 4 Then Exit For
End If
varPreviousByte = varByte
End If
Next varByte
CSSoundex = Left(CSSoundex & "0000", 4)
End Function


Function Soundex(ByVal S As String) As String
S = UCase$(Trim$(S))
Dim Code As Integer: Code = 0
Dim Last As Integer: Last = 0
Dim R As String: R = ""
Dim i As Long: For i = 1 To Len(S)
Select Case Mid$(S, i, 1)
Case "B", "F", "P", "V"
Code = 1
Case "C", "G", "J", "K", "Q", "S", "X", "Z"
Code = 2
Case "D", "T"
Code = 3
Case "L"
Code = 4
Case "M", "N"
Code = 5
Case "R"
Code = 6
Case Else
Code = 0
End Select
If (i = 1) Then
R = Mid$(S, 1, 1)
ElseIf (Code <> 0 And Code <> Last) Then
R = R & Code
End If
Last = Code
Next i
Soundex = Mid$(R & "0000", 1, 4)
End Function


--
Lyle
Hannah Melton <hmme...@worldnet.att.net> wrote in message
news:378547D9...@worldnet.att.net...

larry....@ntpcug.org

unread,
Jul 9, 1999, 3:00:00 AM7/9/99
to
What you describe is called a "Soundex" search. There was one published
for Access 1.x (that shouldn't be much different now) in the Access
Knowledge Base -- start at www.microsoft.com or support.microsoft.com.
Joe Foster also has a version of a Soundex search, I believe, at
members.ricochet.net/~jfoster. His, too, may be in Access Basic from
Access 1.x - 2.0 and might require minor revisions.

Have you considered using a Combo Box with AutoExpand = Yes, so it will
scroll to find valid names as the user types? You can use LimitToList =
No if you want the user to be able to enter a street name that isn't in
your table; you can (using as a model the example in Help and the
Solutions sample database that comes with Access) use LimitToList = Yes
and a NotInList event to force adding the new street name to your table
if it's not in the list. Note the scroll/search will NOT be Soundex,
but will be "as typed" compared to "actual data".

hmme...@worldnet.att.net wrote:
> I have a form which asks for an address by parts, i.e. street number,
> street direction, street name and street type. I also have a table
of
> all the street names in the city. I would like to check the user's
> input against this file and if the street name doesn't exist in the
> table of accepted street names, pop up some possible matches for the
> user to choose from. The possible matches being names that sound
like
> what the user typed in. I can't find anything like this in Access.
Does
> anyone have any ideas? Thanks in advance!


Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

jay

unread,
Jul 13, 1999, 3:00:00 AM7/13/99
to
You could do a table of street names indexed by their soundex code, then
use that as the selection criteria.

Do a web search on 'soundex'

jay

Hannah Melton <hmme...@worldnet.att.net> wrote in article
<378547D9...@worldnet.att.net>...


> I have a form which asks for an address by parts, i.e. street number,
> street direction, street name and street type. I also have a table of
> all the street names in the city. I would like to check the user's
> input against this file and if the street name doesn't exist in the
> table of accepted street names, pop up some possible matches for the
> user to choose from. The possible matches being names that sound like
> what the user typed in. I can't find anything like this in Access. Does
> anyone have any ideas? Thanks in advance!
>

> HM Melton
>
>

Reply all
Reply to author
Forward
0 new messages