数据库按时间查询无法返回值的问题
代码如下：
FileName = "2011-11"
If Form2.Command1 Then
sj1 = Format(Form2.DTPicker3, "hh:nn:ss")
sj2 = Format(Form2.DTPicker5, "hh:nn:ss")
Else
sj1 = "00:00:00"
sj2 = Format(Now, "hh:nn:ss")
End If
chaxun1 = " Select " & CXL & " From 振动量 Where 时间 Between # " & Form2.DTPicker1.Value & " " & sj1 & " # And # " & Form2.DTPicker2.Value & " " & sj2 & " # order by 时间"
YZ1 = chaxun1
XZ = " Select 时间 From 振动量 Where 时间 Between # " & Form2.DTPicker1.Value & " " & sj1 & " # And # " & Form2.DTPicker2.Value & " " & sj2 & " # order by 时间"
Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\VB\" & FileName & ".mdb;Persist Security Info=False"
Adodc2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\VB\" & FileName & ".mdb;Persist Security Info=False"
Adodc1.RecordSource = YZ1
Adodc2.RecordSource = XZ
Adodc1.Refresh
Adodc2.Refresh
Ma = " select max(" & CXL & ") from 振动量 Where 时间 Between # " & Form2.DTPicker1.Value & " " & sj1 & " # And # " & Form2.DTPicker2.Value & " " & sj2 & " # "
Mi = " select min(" & CXL & ") from 振动量 Where 时间 Between # " & Form2.DTPicker1.Value & " " & sj1 & " # And # " & Form2.DTPicker2.Value & " " & sj2 & " # "
ts = " select count(*) from 振动量 Where 时间 Between # " & Form2.DTPicker1.Value & " " & sj1 & " # And # " & Form2.DTPicker2.Value & " " & sj2 & " # "
ymax1 = sql_result(Ma)
ymin1 = sql_result(Mi)
zl = sql_result(ts)
模块：
Public Function sql_result(sql As String) As String
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Set conn = CreateObject("adodb.connection")
conn.Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\VB\" & FileName & ".mdb;Persist Security Info=False"
rs.Open sql, conn, adOpenStatic, adLockOptimistic
conn.Execute (sql)
sql_result = rs(0).Value
conn.Close
End Function
首先是执行后rs.value这里使用无效null,其次之前试过后zl得不到时间范围内记录条数，zl=0.请各位帮忙看看，谢谢了。
[解决办法]
首先得假设你的数据库连接没问题.
可以试一试这样的方法:
将传给过程的那个查询字符串在获得后立即显示到某个文本框中,复制这个查询语句,到数据库里去执行一下,如果没错,那就是返回数据集的问题,如果有错,再仔细分析是什么错.
[解决办法]
chaxun1 = "Select " & CXL & " From 振动量 Where 时间 Between #" & Format(Form2.DTPicker1.Value, "yyyy-mm-dd") & " " & sj1 & "# And #" & Format(Form2.DTPicker2.Value, "yyyy-mm-dd") & " " & sj2 & "# order by 时间"
[解决办法]
Public Function sql_result(sql As String) As String
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Set conn = CreateObject("adodb.connection")
conn.Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\VB\" & FileName & ".mdb;Persist Security Info=False"
rs.Open sql, conn, adOpenStatic, adLockOptimistic
'conn.Execute (sql) '不需要此句
'必须对执行结果进行判断
if rs.recordcount>0 then
if not isnull(rs(0)) then
sql_result = rs(0)
end if
end if
conn.Close
End Function
另外，前述的" # "在井号前后加有空格，是否会影响执行条件，没有测试，将其前后空格去掉试试看
Ma = "select max(" & CXL & ") from 振动量 Where 时间 Between #" & Form2.DTPicker1.Value & " " & sj1 & "# And #" & Form2.DTPicker2.Value & " " & sj2 & "#"
[解决办法]
你拿到查询分析器里试
