Access Database + Soundex(Sound like Query)

[sonia sardana]

hi i want to know that how to use sound like query in VB.net with access.

EXAMPLE (SQL) -

create table names (name varchar(100))
insert into names values ('ritu')
select * from names where soundex(name) =soundex('reetu')

OUTPUT-

ritu

[Teme64]

AFAIK MS Access does not have a built-in Soundex function. You have to write your own. Here's an article from MSDN: Building Microsoft Access Applications with VB (VBA?) code for Soundex function. See if it's for any help.

[dkmansion]

hi i want to know that how to use sound like query in VB.net with access.

EXAMPLE (SQL) -

create table names (name varchar(100))
insert into names values ('ritu')
select * from names where soundex(name) =soundex('reetu')

OUTPUT-

ritu

Hi I got this from The O'Reilly Access Cook Book. Has worked wonders for me.

Paste it into a module then use acbsoundex() in stead of soundex in your SQL statement.

Enjoy

Public Function acbSoundex( _
  ByVal varSurName As Variant) As Variant

    ' Purpose:
    '     Takes a surname string and returns a 4-digit
    '     code representing the Russell Soundex code.
    ' In:
    '     varSurName: A surname (last name) as a variant
    ' Out:
    '     Return value: A 4-digit Soundex code as a variant

    On Error GoTo HandleErr

    Dim intLength As Integer
    Dim intCharCount As Integer
    Dim intSdxCount As Integer
    Dim intSeparator As Integer
    Dim intSdxCode As Integer
    Dim intPrvCode As Integer
    Dim varChar As Variant
    Dim varSdx As Variant
    
    Const acbcSoundexLength = 4

    ' We add "" to take care of a passed Null
    intLength = Len(varSurName & "")
    
    If intLength > 0 Then
        intSeparator = 0     'Keeps track of vowel separators
        intPrvCode = 0       'The code of the previous char
        intCharCount = 0     'Counts number of input chars
        intSdxCount = 0      'Counts number of output chars
    
        'Loop until the soundex code is of acbcSoundexLength
        'or we have run out of characters in the surname
        Do Until (intSdxCount = acbcSoundexLength Or intCharCount = intLength)
            intCharCount = intCharCount + 1
            varChar = Mid(varSurName, intCharCount, 1)
    
            'Calculate the code for the current character
            Select Case varChar
                Case "B", "F", "P", "V"
                    intSdxCode = 1
                Case "C", "G", "J", "K", "Q", "S", "X", "Z"
                    intSdxCode = 2
                Case "D", "T"
                    intSdxCode = 3
                Case "L"
                    intSdxCode = 4
                Case "M", "N"
                    intSdxCode = 5
                Case "R"
                    intSdxCode = 6
                Case "A", "E", "I", "O", "U", "Y"
                    intSdxCode = -1
                Case Else
                    intSdxCode = -2
            End Select
    
            'Special case the first character
            If intCharCount = 1 Then
                varSdx = UCase(varChar)
                intSdxCount = intSdxCount + 1
                intPrvCode = intSdxCode
                intSeparator = 0
            'If a significant constant and not a repeat
            'without a separator then code this character
            ElseIf intSdxCode > 0 And _
             (intSdxCode <> intPrvCode Or intSeparator = 1) Then
                varSdx = varSdx & intSdxCode
                intSdxCount = intSdxCount + 1
                intPrvCode = intSdxCode
                intSeparator = 0
            'If a vowel, this character is not coded,
            'but it will act as a separator
            ElseIf intSdxCode = -1 Then
                intSeparator = 1
            End If
        Loop
    
        'If the code is < acbcSoundexLength chars long, then
        'fill the rest of code with zeros
        If intSdxCount < acbcSoundexLength Then
            varSdx = varSdx & String((acbcSoundexLength - intSdxCount), "0")
        End If
        
        acbSoundex = varSdx
    Else
        acbSoundex = Null
    End If
    
ExitHere:
    On Error GoTo 0
    Exit Function

HandleErr:
    Select Case Err
    Case Else
        MsgBox Err & ": " & Err.Description, _
         vbOKOnly + vbCritical, "acbSoundex"
    End Select
    Resume ExitHere
End Function