出错提示:标准表达式中数据类型不匹配,出错行:ret.Open local_db, cnn
按昨天好心人教的改了半天..现在程序运行没反映了..晕Private Sub Command1_Click()
Dim cnn As New ADODB.Connection
Dim ret As New ADODB.Recordset
Dim local_db As String
Set cnn = New ADODB.Connection
Set ret = New ADODB.Recordset
ret.CursorLocation = adUseClient
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + App.Path & "\车辆管理(含图片).mdb" + ";Persist Security Info=False;"
local_db = "select * from 出入表 where 1=1"
If IsDate(Text2) Then local_db = local_db & " and 出入表.进入时间>=" & CDate(Text2) & ""
If IsDate(Text3) Then local_db = local_db & " and 出入表.进入时间<=" & CDate(Text3) & ""
ret.Open local_db, cnn
If ret.BOF And ret.EOF Then
MsgBox "此时间内无车辆出入", , "警告"
Text2.Text = ""
Text2.SetFocus
Else
Set 查询结果.DataGrid1.DataSource = ret
End If
End Sub
按昨天好心人教的改了半天..现在程序运行没反映了..晕Private Sub Command1_Click()
Dim cnn As New ADODB.Connection
Dim ret As New ADODB.Recordset
Dim local_db As String
Set cnn = New ADODB.Connection
Set ret = New ADODB.Recordset
ret.CursorLocation = adUseClient
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + App.Path & "\车辆管理(含图片).mdb" + ";Persist Security Info=False;"
local_db = "select * from 出入表 where 1=1"
If IsDate(Text2) Then local_db = local_db & " and 出入表.进入时间>=" & CDate(Text2) & ""
If IsDate(Text3) Then local_db = local_db & " and 出入表.进入时间<=" & CDate(Text3) & ""
ret.Open local_db, cnn
If ret.BOF And ret.EOF Then
MsgBox "此时间内无车辆出入", , "警告"
Text2.Text = ""
Text2.SetFocus
Else
Set 查询结果.DataGrid1.DataSource = ret
End If
End Sub
if 进入时间 的字段类型是 日期型 并且是SQL SERVER then local_db & " and 出入表.进入时间>='" & CDate(Text2) & "'"
if 进入时间 的字段类型是 日期型 并且是ACCESS then local_db & " and 出入表.进入时间>=#" & CDate(Text2) & "#"
if 进入时间 的字段类型是 日期型 并且是ACCESS then local_db & " and 出入表.进入时间>=" & CDate(Text2) & "" ,不加#也可以的。
不过又有个小问题。查询结果没有text3这天的记录,如text3输入2008-7-29,显示没有这天的记录。怎么回事?
改为:If IsDate(Text2) Then local_db = local_db & " and (between 出入表.进入时间 '" & format(Text2,"yyyy-mm-dd") & " 00:00:00' "
If IsDate(Text3) Then local_db = local_db & " and 出入表.进入时间 <=" & CDate(Text3) & ""
改为:If IsDate(Text3) Then local_db = local_db & " and '" & format(Text3,"yyyy-mm-dd") & " 23:59:59')"
改为:If IsDate(Text3) Then local_db = local_db & " and '" & format(Text3,"yyyy-mm-dd") & " 23:59:59" & "')"
strD1 = Text2.text & " 00:00:00" '起始日期
strD2 = Text3.text & " 23:59:59" '结束日期
local_db = " and (出入表.进入时间 between '" & Format(strD1, "yyyy-mm-dd hh:mm:ss") & _
"' and '" & Format(strD2, "yyyy-mm-dd hh:mm:ss") & "') "
或者,把数据库中的值取出来格式化成日期格式.
不过你这里有点问题。日期用#,字符用’。另外 strD1 = Text2.text & " 00:00:00"的功能是“从凌晨开始”这个意思吗?
正确代码:
local_db = local_db &" and (出入表.进入时间 between #" & Format(strD1, "yyyy-mm-dd hh:mm:ss") & _
"# and #" & Format(strD2, "yyyy-mm-dd hh:mm:ss") & "#) "