MS Access 2003 Using SoundEx to search from a form

Question

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.

Answer 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) & "';"