1

I need to get a search to look for "fuzzy matches" in a database when searching by Surname. I have implemented the following function to enable a SoundEx function, which has no issues.

I am getting issues when it comes to building the SQL to search through the database calling the SoundEx function on the fly. I know that VBA functions can be called in SQL statements, yet it doesn't seem to be working properly.

Private Sub cmdSearch_Click()

Dim LSQL  As String
Dim LSearchString As String

If Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
    MsgBox "You must enter a search string."

Else
    LSearchString = txtSearchString
    LSQL = "SELECT * FROM TVictim "
    LSQL = LSQL & "WHERE Soundex([Victim Surname]) = " & Soundex(LSearchString) & ";"

    frmVictim.Form.RecordSource = LSQL


    lblTitle.Caption = "Matching Victim Details:  Filtered by '" & LSearchString & "'"
    txtSearchString = ""

    MsgBox "Results have been filtered.  All Victim Names containing " & LSearchString & "."

End If End Sub

When I enter a string on the form and click the button, I have stepped through, and at the point it builds the SQL, its throws up a command window with the SoundEx output of the text in the search box, and another box for data entry.

Been fiddling with this for a while and can't seem to find an example that helps.

Dori
  • 915
  • 1
  • 12
  • 20
Daniel
  • 13
  • 3
  • Performance-wise, it's a good idea to store the Soundex values in your data table, because then they can be indexed, and thus you vastly speed up searches using them. Also note that garden-variety Soundex() is a VERY fuzzy match and works only for names in certain languages. You might want to look into Soundex2. For large data tables where I need fuzzy matches, I have both Soundex and Soundex2 fields for first and last names, and it works very well. – David-W-Fenton Apr 30 '11 at 19:49

1 Answers1

1

I'm using Allen Browne's Soundex function with Access 2003: Soundex - Fuzzy matches

It returns the Soundex value as a string. If the Soundex function you're using also returns a string, enclose Soundex(LSearchString) with quotes so the database engine recognizes it as a string value instead of the name of a missing parameter.

LSQL = LSQL & "WHERE Soundex([Victim Surname]) = '" & Soundex(LSearchString) & "';"
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • Yeah thats the same function I am using, forgot to mention that, works perfectly though, thanks. – Daniel May 02 '11 at 23:31