[AccessD] "fuzzy logic" search

Found this.  Can't recall where I got it from or if I modded it, however if
you search google for soundex vba you should get some hits:


***********************************************************
Public Function Soundex(Word As String) As String
    
Dim strCode As String
Dim strChar As String
Dim lngWordLength As Long
Dim strLastCode As String

'Grabs the first letter
strCode = UCase(Mid$(Word, 1, 1))
strLastCode = GetSoundCodeNumber(strCode)

'Stores the word length
lngWordLength = Len(Word)

'Continues the code, starting at the second letter
For i = 2 To lngWordLength
    strChar = GetSoundCodeNumber(UCase(Mid$(Word, i, 1)))

    ' If adjacent numbers are the same,
    ' only count one of them
    If Len(strChar) > 0 And strLastCode <> strChar Then
        strCode = strCode & strChar
    End If
    strLastCode = strChar

Next

'Trim it down to a maximum of four characters...
Soundex = Mid$(strCode, 1, 4)

'... but if it's less than four characters, pad
'it out with a bunch of zeros...
If Len(strCode) < 4 Then
    Soundex = Soundex & String(4 - Len(strCode), "0")
End If

End Function

Private Function GetSoundCodeNumber(Character As String) As String
    
'Accepts a character and returns the
'appropriate number from the Soundex table

Select Case Character
    Case "B", "F", "P", "V"
       GetSoundCodeNumber = "1"
    Case "C", "G", "J", "K", "Q", "S", "X", "Z"
       GetSoundCodeNumber = "2"
    Case "D", "T"
        GetSoundCodeNumber = "3"
    Case "L"
        GetSoundCodeNumber = "4"
    Case "M", "N"
        GetSoundCodeNumber = "5"
    Case "R"
        GetSoundCodeNumber = "6"
End Select

End Function
***********************************************************


Cheers,
Andrew

-----Original Message-----
From: David McAfee [mailto:DMcAfee at haascnc.com]
Sent: Wednesday, 22 October 2003 10:07 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] "fuzzy logic" search


Its an MS SQL function. Open QA, type in SOUNDEX, high light the word and
press SHIFT+F1. Helps covers it a bit.

D

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Colby
Sent: Tuesday, October 21, 2003 5:32 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] "fuzzy logic" search


Hmmm soundex would be good though.  Do you have this function?  Is it a
class, a single function?  How does it work?

John W. Colby
www.colbyconsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte
Foust
Sent: Tuesday, October 21, 2003 8:20 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] "fuzzy logic" search


John,

I've received this kind of request before and I looked into it once.
The fuzzy logic math was way, way beyond my grasp, so I settled for
offering alternatives like soundex on the names.  I created a customized
soundex that used a number for leading vowels rather than the standard
"vowel followed by number" pattern, so that if they misspelled a name
with a leading vowel, there were still possible matches.  But SSN?  I
mean, think of it.  If a SSN has 9 digits, any one of which can repeat,
how do you determine a mismatch by up to 2 characters?  WHICH 2
characters?

Charlotte Foust

-----Original Message-----
From: John Colby [mailto:jcolby at colbyconsulting.com]
Sent: Tuesday, October 21, 2003 4:06 PM
To: AccessD
Subject: [AccessD] "fuzzy logic" search


My client is having difficulties where the data input people are
entering duplicate claims because wrong names or SSNs are entered.  He's
asked how difficult it would be to do a search for the names entered
(seems irrelevant if it is wrong) but then has asked how difficult it
would be to do a "fuzzy logic" search for SSNs that are different by up
to 2 characters.  I don't even have a clue how to attack something like
that.  Any ideas (other than "out the door, no parachute")?

John W. Colby
www.colbyconsulting.com


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

IMPORTANT - PLEASE READ ******************** 
This email and any files transmitted with it are confidential and may 
contain information protected by law from disclosure. 
If you have received this message in error, please notify the sender 
immediately and delete this email from your system. 
No warranty is given that this email or files, if attached to this 
email, are free from computer viruses or other defects. They 
are provided on the basis the user assumes all responsibility for 
loss, damage or consequence resulting directly or indirectly from 
their use, whether caused by the negligence of the sender or not.