时间戳

首页 > 解决方案 > DocmdApplyFilter 语法错误

ms-access - DocmdApplyFilter 语法错误

问题描述

在我的访问表单中，我得到了一个

               Run-time error'3705:

日期查询表达式 '(( ( [Date of Purchase] >=##And[Date of urchase]<=##))' 中的语法错误。

每当我运行我的代码时，没有填充 Me.TxtPurchaseDateTo 和 Me.TxtPurchaseDateTo 字段而不是应该运行的 msg 框。此外，当我单击“清除”按钮时，会弹出一个“输入参数值”对话框，无论是否显示数据。为了清除数据表格，我必须在“输入参数值”对话框的输入框中按空格键才能清除表格。如果我点击取消，我会收到运行时错误 2501“ApplyFilter 操作已取消，如果我调试错误，我将被带到我的代码表，其中突出显示“DoCmd.ApplyFilter 任务”。

我已经删除了代码的几个部分重新检查了拼写和间距

Option Compare Database


 Private Sub CmdSearch_Click()
 'Search button

    Call Search
    End Sub
        Sub Search()
        Dim strCriteria, task As String

    Me.Refresh
If IsNull(Me.TxtPurchaseDateFrom) Or IsNull(Me.TxtPurchaseDateTo) Then
    MsgBox "Please enter the date range", vbInformation, "Date Range 
Required"
    Me.TxtPurchaseDateFrom.SetFocus
Else
    strCriteria = "([Date of Purchase] >= #" & Me.TxtPurchaseDateFrom & "# 
And [Date of Purchase] <=#" & Me.TxtPurchaseDateTo & "#)"
    task = "select * From TblPurchases Where( " & strCriteria & ") order 
by [Date of Purchase] "
    DoCmd.ApplyFilter task
    'Me.TxtTotal = FindRecordCount

End If


End Sub

    Private Sub CmdClear_Click()

    Dim task As String

    Me.TxtPurchaseDateFrom = ""
    Me.TxtPurchaseDateTo = ""
    task = "select * from TblPurchases where PrimaryKey is null"
    DoCmd.ApplyFilter task

     'Me.TxtTotal = FindRecordCount



End Sub

    Private Sub CmdShowAll_Click()
Dim task As String

    Me.TxtPurchaseDateFrom = ""
    Me.TxtPurchaseDateTo = ""
    task = "select * from TblPurchases order by [Date of Purchase] "
    Me.RecordSource = task
     'Me.TxtTotal = FindRecordCount

End Sub

我期望如果我只是取消对话框，表单应该保留在屏幕上。此外，如果“from”和“t/o”字段为空，我应该得到 MsgBox 结果。

我不确定围绕“DoCmd.ApplyFilter 任务”的语法错误是什么我没有看到什么错误？

标签： ms-accessvba

解决方案

您的条件不能是完整的 SQL 语句，只能是条件：

strCriteria = "[Date of Purchase] >= #" & Me.TxtPurchaseDateFrom & "# And [Date of Purchase] <= #" & Me.TxtPurchaseDateTo & "#"
DoCmd.ApplyFilter strCriteria

您可以使用Nz来避免来自空文本框的错误：

strCriteria = "[Date of Purchase] >= #" & Nz(Me!TxtPurchaseDateFrom.Value, Date) & "# And [Date of Purchase] <= #" & Nz(Me!TxtPurchaseDateTo.Value, Date) & "#"
DoCmd.ApplyFilter strCriteria

或者：

If IsNull(Me!TxtPurchaseDateFrom.Value) Then
    strCriteria = "[Date of Purchase] <= #" & Nz(Me!TxtPurchaseDateTo.Value, Date) & "#"
Else
    strCriteria = "[Date of Purchase] >= #" & Nz(Me!TxtPurchaseDateFrom.Value, Date) & "# And [Date of Purchase] <= #" & Nz(Me!TxtPurchaseDateTo.Value, Date) & "#"
End If
DoCmd.ApplyFilter strCriteria

推荐阅读