I have a function which gives Levenshtein Distance used for fuzzy search of a field.

Option Compare Database

' Levenshtein3 tweaked for UTLIMATE speed and CORRECT results
' Solution based on Longs
' Intermediate arrays holding Asc()make difference
' even Fixed length Arrays have impact on speed (small indeed)
' Levenshtein version 3 will return correct percentage
Function Levenshtein(ByVal string1 As String, ByVal string2 As String) As Long
Dim i As Long, j As Long, string1_length As Long, string2_length As Long
Dim distance(0 To 60, 0 To 50) As Long, smStr1(1 To 60) As Long, smStr2(1 To 50) As Long
Dim min1 As Long, min2 As Long, min3 As Long, minmin As Long, MaxL As Long

string1_length = Len(string1):  string2_length = Len(string2)

distance(0, 0) = 0
For i = 1 To string1_length:    distance(i, 0) = i: smStr1(i) = Asc(LCase(Mid$(string1, i, 1))): Next
For j = 1 To string2_length:    distance(0, j) = j: smStr2(j) = Asc(LCase(Mid$(string2, j, 1))): Next
For i = 1 To string1_length
    For j = 1 To string2_length
        If smStr1(i) = smStr2(j) Then
            distance(i, j) = distance(i - 1, j - 1)
            min1 = distance(i - 1, j) + 1
            min2 = distance(i, j - 1) + 1
            min3 = distance(i - 1, j - 1) + 1
            If min2 < min1 Then
                If min2 < min3 Then minmin = min2 Else minmin = min3
                If min1 < min3 Then minmin = min1 Else minmin = min3
            End If
            distance(i, j) = minmin
        End If

' Levenshtein3 will properly return a percent match (100%=exact) based on similarities and Lengths etc...
MaxL = string1_length: If string2_length > MaxL Then MaxL = string2_length
Levenshtein = 100 - CLng((distance(string1_length, string2_length) * 100) / MaxL)

End Function

When I try to write a SQL statement like the one below, I get a datatype mismatch error:

strSQL_1 = "SELECT * FROM Location_Table ORDER BY (Levenshtein([Short_Desc],'Search String')) DESC;"
Set rs = CurrentDb.OpenRecordset(strSQL_1)

Where Short_Desc is a column in the Location_Table with Short Text datatype.

Using Debug.Print strSQL_1, this is what's shown in the immediate window:

SELECT * FROM Location_Table ORDER BY Levenshtein(Short_Desc,'Search String') DESC;

1 Answer 1


The problem is not the value of Short_Desc is used, but the fieldname.


"SELECT * FROM Location_Table ORDER BY (Levenshtein(" & [Short_Desc] & ",'Search String')) DESC"
  • This gives the error: Microsoft Access can't find the field '|1' referred to in your expression. Oct 25, 2022 at 11:16
  • The syntax of the ORDER BY clause is as follows - SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC; Your function Levenshtein returns a long integer which SQL interprets as a field name. @Knightowl3128
    – dave29
    Oct 26, 2022 at 6:10
  • @user10186832 So, how do I rewrite this query to sort by my custom function? Oct 29, 2022 at 6:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.