日期字段与 SQL Server express 后端的访问类型不匹配 [英] Access type mismatch on date field with SQL Server express backend

问题描述

我有一个带有未绑定文本框的简单访问表单，用于搜索具有匹配日期的记录，结果显示在子表单中.后端数据库存储在 SQL Express 2008 实例中，通过 ODBC 连接(SQL Server Native Client 10.0)访问.客户端是 Access 2007，运行在 64 位 Windows 7 专业版上.

I have a simple access form with an unbound textbox that is used to search for records with a matching date, with the results displayed in a subform. The backend database is stored in an SQL Express 2008 instance, accessed via an ODBC connection (SQL Server Native Client 10.0). The client is Access 2007, running on 64 bit Windows 7 professional.

当用户点击表单上的搜索按钮时，会执行以下 VBA 代码来更新子表单.

When the user clicks on the search button on the form, the following VBA code is executed to update the subform.

Private Sub UpdateSfmQuery()
    Dim query As String

    If IsDate(txtDate.Value) And IsNumeric(cboStaff.Value) Then
        query = "SELECT * FROM TimesheetEntries " & _
                "WHERE StaffId = " & cboStaff.Value & " " & _
                "AND [Date] = '" & _
                Format(txtDate.Value, "YYYY/MM/DD HH:MM:SS") & "' "
    Else
        query = "SELECT * FROM TimesheetEntries WHERE 0 = 1;"
    End If

    frmTimesheetUpdateSfm.Form.RecordSource = query  '<--- Fails here
End Sub

SQL Server 后端 [Date] 字段中的数据类型是 DateTime，上面的代码在上面突出显示的行上失败并出现错误，条件表达式中的数据类型不匹配.

The datatype in the SQL Server back end [Date] field is DateTime, and the code above fails on the highlighted line above with the error, Data type mismatch in criteria expression.

我已经尝试将 txtDate 的格式属性从短日期更改为一般日期，并且我还修改了上面那行格式化 txtDate.Value 的日期格式.

I've tried changing the format property of txtDate from short date to general date, and I've also modified the date format in the line above that formats txtDate.Value.

上面代码摘录中变量query中包含的SQL查询(如下所示的值)，复制粘贴到SQL Express Management Studio中时可以完美执行.

The SQL query contained in the variable query in the above code excerpt (value shown below), executes perfectly when copied and pasted into SQL Express Management Studio.

SELECT * FROM TimesheetEntries WHERE StaffId = 14 AND [Date] = '2011/11/22 00:00:00'

我做错了什么?

推荐答案

问题是本机 Access 查询中的日期值需要用井号/哈希符号而不是引号括起来.基本上，与任何 SQL 数据库一样，Access 拥有自己的 SQL 方言，并且与 SQL Server 略有不同.

The problem is that date values inside native Access queries need to be surrounded by pound/hash signs instead of quotes. Basically, like any SQL database, Access has it's own dialect of SQL and it is slightly different from SQL Server.

Private Sub UpdateSfmQuery()
    Dim query As String

    If IsDate(txtDate.Value) And IsNumeric(cboStaff.Value) Then
        query = "SELECT * FROM TimesheetEntries " & _
                "WHERE StaffId = " & cboStaff.Value & " " & _
                "AND [Date] = #" & _
                Format(txtDate.Value, "YYYY/MM/DD HH:MM:SS") & "# "
    Else
        query = "SELECT * FROM TimesheetEntries WHERE 0 = 1;"
    End If

    frmTimesheetUpdateSfm.Form.RecordSource = query  '<--- Used to fail here
End Sub

请记住，您始终可以制作查询模型并使用 Access 的查询编辑器来尝试使查询工作.

Remember, you can always take a mockup of your query and use Access's query editor to try to make the query work.

作为旁注，我建议您使用 ms-access 标签标记这样的问题，除非您的问题特定于某个版本的 Access.

As a side note, I recommend you tag questions like this with the ms-access tag unless your problem is specific to a certain version of Access.

这篇关于日期字段与 SQL Server express 后端的访问类型不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！