需要通过vba比较访问中的日期

Question

我有以下问题:无法获得sql查询与日期的工作。以下查询:

strRowSource = "SELECT [ID],[TaskType],[TaskName],[StartDate],[EndDate],[isFinished] " & _
"FROM tblTasks " & "WHERE [Person] = " & TempVars("CurrentUser") & " AND [EndDate] < #" & Format(Now(), "short date") & "#"

不返回任何值。我想要的是已经过期的任务列表。

Thanks in advance

Answer 1

我正在使用这个方便的函数来格式化日期:

Public Function JetSqlDate(ByVal d As Variant) As String
If IsNull(d) Then
JetSqlDate = "NULL"
Else
JetSqlDate = Format$(d, "#mm/dd/yyyy hh:nn:ss#")
End If
End Function

用法:

strRowSource = "SELECT ID, TaskType, TaskName, StartDate, EndDate, isFinished " & _
"FROM tblTasks WHERE Person = " & TempVars("CurrentUser") & _
" AND EndDate < " &  JetSqlDate(Now())

注意，转义列名([])仅在包含无效字符(如空格)或与关键字冲突时才需要。例如，如果您有一个名为From的列，则必须将其写成[From]。

---

对于SQL-Server直通查询，我使用:

Public Function TSqlDate(ByVal d As Variant) As String
If IsNull(d) Then
TSqlDate = "NULL"
Else
TSqlDate = "{ ts '" & Format$(d, "yyyy-mm-dd hh:nn:ss") & "' }"
End If
End Function

为字符串:

Public Function SqlStr(ByVal s As String) As String
'Input: s=""      Returns: NULL
'Input: s="abc"   Returns: 'abc'
'Input: s="x'y"   Returns: 'x''y'
If s = "" Then
SqlStr = "NULL"
Else
SqlStr = "'" & Replace(s, "'", "''") & "'"
End If
End Function

注意，该函数添加分隔符并转义字符串值中包含的任何分隔符。这使得查询更可靠，也可以防止SQL注入。

Answer 2

你可以把它缩减为:

strRowSource = "SELECT ID, TaskType, TaskName, StartDate, EndDate, isFinished " & _
"FROM tblTasks " & _
"WHERE Person = " & TempVars("CurrentUser") & " AND EndDate < Now()"