Access VBA中设置Like "*"出现类型不匹配问题求助

问题描述 #

变量已声明为String类型，对应字段均为短文本类型，Like "*"语句在Access查询中可正常运行，但在VBA代码里使用偏好写法时触发类型不匹配错误。

偏好写法（报错版本） #

Private Sub AppendFilter_Change()
    Dim UserF As String
    Dim DateF As String
    
    UserF = Me.UserFilterCombo
    DateF = Me.AppendFilter
    
    If UserF = "" Then
       UserF = "Like ""*""" '<---类型不匹配错误
    Else
    End If
    
    Me.TasksLst.RowSource = "SELECT tblTasks.ID, tblTasks.Owner, tblTasks.[Task Name], tblTasks.Priority, tblTasks.Company, tblTasks.Status, tblTasks.Notes, tblTasks.DueDate, tblTasks.StartDate, tblTasks.DateCompleted, tblTasks.DateCreated, tblTasks.[Need Help], tblTasks.Assigned " _
    & "FROM tblTasks " _
    & "WHERE (((tblTasks.Owner)='" & UserF & "') AND " & DateF & " AND ((tblTasks.[Recurring Event])=False)) OR (((tblTasks.Owner)='" & UserF & "') AND ((tblTasks.Status)='Not Started') AND " & DateF & " AND ((tblTasks.[Recurring Event])=False)) OR (((tblTasks.Owner)='" & UserF & "') AND ((tblTasks.Status)='In Progress') AND " & DateF & " AND ((tblTasks.[Recurring Event])=False)) " _
    & "ORDER BY tblTasks.Owner, tblTasks.DueDate;"
    
    Me.TasksLst.Requery
End Sub

可运行但非偏好的写法 #

Private Sub AppendFilter_Change()
Dim UserF As String
Dim DateF As String

UserF = Me.UserFilterCombo.Value
DateF = Me.AppendFilter

If UserF = "" Then
Me.TasksLst.RowSource = "SELECT tblTasks.ID, tblTasks.Owner, tblTasks.[Task Name], tblTasks.Priority, tblTasks.Company, tblTasks.Status, tblTasks.Notes, tblTasks.DueDate, tblTasks.StartDate, tblTasks.DateCompleted, tblTasks.DateCreated, tblTasks.[Need Help], tblTasks.Assigned " _
& "FROM tblTasks " _
& "WHERE (((tblTasks.Owner) Like ""*"") AND " & DateF & " AND ((tblTasks.[Recurring Event])=False)) OR (((tblTasks.Owner) Like ""*"") AND ((tblTasks.Status)='Not Started') AND " & DateF & " AND ((tblTasks.[Recurring Event])=False)) OR (((tblTasks.Owner) Like ""*"") AND ((tblTasks.Status)='In Progress') AND " & DateF & " AND ((tblTasks.[Recurring Event])=False)) " _
& "ORDER BY tblTasks.Owner, tblTasks.DueDate;"

Else
Me.TasksLst.RowSource = "SELECT tblTasks.ID, tblTasks.Owner, tblTasks.[Task Name], tblTasks.Priority, tblTasks.Company, tblTasks.Status, tblTasks.Notes, tblTasks.DueDate, tblTasks.StartDate, tblTasks.DateCompleted, tblTasks.DateCreated, tblTasks.[Need Help], tblTasks.Assigned " _
& "FROM tblTasks " _
& "WHERE (((tblTasks.Owner)='" & UserF & "') AND " & DateF & " AND ((tblTasks.[Recurring Event])=False)) OR (((tblTasks.Owner)='" & UserF & "') AND ((tblTasks.Status)='Not Started') AND " & DateF & " AND ((tblTasks.[Recurring Event])=False)) OR (((tblTasks.Owner)='" & UserF & "') AND ((tblTasks.Status)='In Progress') AND " & DateF & " AND ((tblTasks.[Recurring Event])=False)) " _
& "ORDER BY tblTasks.Owner, tblTasks.DueDate;"

End If

Me.TasksLst.Requery
End Sub

错误原因 #

偏好写法中，当UserF为空时赋值"Like ""*"""，后续拼接SQL会生成(tblTasks.Owner)='Like "*"'。这里把SQL运算符Like当成了字符串值的一部分，数据库会尝试将Owner字段与字符串Like "*"做相等匹配，而非执行模糊查询逻辑，因此触发类型不匹配错误。

修正后的偏好写法 #

Private Sub AppendFilter_Change()
    Dim UserF As String
    Dim DateF As String
    
    UserF = Me.UserFilterCombo
    DateF = Me.AppendFilter
    
    ' 仅设置匹配值，将Like运算符固定在SQL语句中
    If UserF = "" Then
       UserF = "*"
    End If
    
    Me.TasksLst.RowSource = "SELECT tblTasks.ID, tblTasks.Owner, tblTasks.[Task Name], tblTasks.Priority, tblTasks.Company, tblTasks.Status, tblTasks.Notes, tblTasks.DueDate, tblTasks.StartDate, tblTasks.DateCompleted, tblTasks.DateCreated, tblTasks.[Need Help], tblTasks.Assigned " _
    & "FROM tblTasks " _
    & "WHERE (((tblTasks.Owner) Like '" & UserF & "') AND " & DateF & " AND ((tblTasks.[Recurring Event])=False)) OR (((tblTasks.Owner) Like '" & UserF & "') AND ((tblTasks.Status)='Not Started') AND " & DateF & " AND ((tblTasks.[Recurring Event])=False)) OR (((tblTasks.Owner) Like '" & UserF & "') AND ((tblTasks.Status)='In Progress') AND " & DateF & " AND ((tblTasks.[Recurring Event])=False)) " _
    & "ORDER BY tblTasks.Owner, tblTasks.DueDate;"
    
    Me.TasksLst.Requery
End Sub

说明 #

修正后，UserF仅存储匹配值（空时为*），SQL语句中固定使用Like运算符，既保持了偏好的简洁写法，又确保SQL逻辑正确，避免了类型不匹配问题。

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