Question

access 使用的between and 和 date 方法

Sub test2()
Dim rs As Recordset, str As String, en As String
str = dateadd("d" ,-1000,date)
en = date
Set rs = CurrentDb.OpenRecordset("select Count(*) AS Count from Enquiry where ((Enquiry.Date) between '" & str & "' and '" & en & "')")
T0.Value = rs("Count")
rs.Close
Set rs = Nothing
End Sub

补充：数据库表Enquiry 有符合要求的表！
但是就是出错，就是set rs=("......")出错，
我用
str = "#" & dateadd("d" ,-1000,date) & "#"
en = "#" & date & "#"
也是不行！
神啊，救救我吧！
Set rs = CurrentDb.OpenRecordset("select Count(*) AS Count from Enquiry where ((Enquiry.Date) between '" & str & "' and '" & en & "')")
是用不了“>=”"<="
大虾们，帮帮忙啊！
谢谢高手们！
"SqlStr = "select Count(*) AS mCount from [Enquiry] where [Date] between #" & strStartDate & "# and #" & strEndDate & "# and [term] like " & sqlterm & ""
Set rs = CurrentDb.OpenRecordset(SqlStr, dbOpenDynaset, dbReadOnly)
T0.Value = rs("mCount").Value
"
出错啊，就是就是说 看到
strStartDate = 2006-12-16
strEndDate = 2008-12-24
strTerm = 08sum

但是rs = Nothing
dbOpenDynaset = 2
dbReadOnly = 4

当一个[term] 就可以有结果！
再次麻烦大哥们！

Answer 1

你的问题是语法问题，而且名命不规则！

估计 CurrentDb 是用DAO打开Access吧！

设计数据库时如果用了数据库中的关键字，查询语句中就必须要用[]符号把这些关键字括起来，就算不是关键字用[]也不会有什么影响！

Private Sub test2()
Dim rs As Recordset
Dim SqlStr As String
Dim strStartDate As String
Dim strEndDate As String

strStartDate = DateAdd("d", -1000, Date)
strEndDate = Date

SqlStr = "select Count(*) AS mCount from [Enquiry] where [Date] between #" & strStartDate & "# and #" & strEndDate & "# "
Set rs = CurrentDb.OpenRecordset(SqlStr, dbOpenDynaset, dbReadOnly)
T0.Value = rs("mCount").Value

If Not (rs Is Nothing) Then
rs.Close: Set rs = Nothing
End If

End Sub

补充：

如果数据库是SQL，查询语句可改为：

SqlStr = "select Count(*) AS mCount from [Enquiry] where [Date] between cast('" & strStartDate & "' as DateTime) and cast('" & strEndDate & "' as DateTime) "

Answer 2

在我看来是你的select中的日期表达式出错了。

我做了一个类似的模拟操作，我觉得你的SELECT语句应该改成：

str =dateadd("d" ,-1000,date)
en =date
"SELECT Count(*) AS Count FROM Enquiry WHERE Enquiry.date between #" & str & "# And #" & en & "#"

整句就应该是：
str =dateadd("d" ,-1000,date)
en =date
Set rs=CurrentDb.OpenRecordset("SELECT Count(*) AS Count FROM Enquiry WHERE Enquiry.date Between #" & str & "# And #" & en &"#")

另外，你用date作字段名，用Count作字段别名，这即使不错也很危险，因为他们是Access的保留词，建议你最好改过来。

Answer 3

将 ' 改成 #

Set rs = CurrentDb.OpenRecordset("select Count(*) AS Count from Enquiry where ((Enquiry.Date) between #" & str & "# and # " & en & "#)")

Answer 4

我想试试这个问题
最好把库和代码给我帮你调试
下个HI，HI我传给我帮你。