SQL调用Levenshtein函数时出现数据类型不匹配错误

函数代码 #

我用于字段模糊搜索的Levenshtein距离函数代码如下：

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)
        Else
            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
            Else
                If min1 < min3 Then minmin = min1 Else minmin = min3
            End If
            distance(i, j) = minmin
        End If
    Next
Next

' 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

报错场景 #

当执行以下VBA代码调用SQL时，触发数据类型不匹配错误：

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

其中Short_Desc是Location_Table表中的短文本类型字段，Debug输出的最终SQL语句为：

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

问题原因及解决办法 #

核心原因 #

Access SQL调用VBA函数时，如果Short_Desc字段存在Null值，会导致参数传递失败——函数要求接收String类型，但Null不属于有效字符串类型，从而触发数据类型不匹配错误。此外还要确保函数放在标准模块中，否则SQL无法调用。

解决办法 #

1. 在函数内部处理Null参数
   在函数开头添加Null检查逻辑，避免无效参数进入计算：

Function Levenshtein(ByVal string1 As String, ByVal string2 As String) As Long
    ' 新增Null检查
    If IsNull(string1) Or IsNull(string2) Then
        Levenshtein = 0 ' 可根据需求调整默认返回值，比如0代表完全不匹配
        Exit Function
    End If
    ' 原有代码...
End Function

2. 在SQL中用Nz函数处理字段Null值
   直接在SQL里将Null字段转为空字符串，确保传递给函数的是有效字符串：

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

3. 确认函数存放位置
   确保Levenshtein函数放在标准模块中（不是窗体或报表模块），只有标准模块中的公共函数才能被Access SQL调用。

4. 验证字段类型
   再次确认Short_Desc字段确实是短文本类型，未被误修改为备注、数字等其他类型。

内容的提问来源于stack exchange，提问作者Knightowl3128