[求助]多条件模糊查询的代码错在哪？

zbs112

ss3VoPcv.rar (16.78 KB, 下载次数: 10)

2006-4-15 16:03 上传

点击文件名下载附件
[求助]多条件模糊查询的代码错在哪？

代码如下，要如何修改？ Private Sub CommandButton1_Click() Set CNN = CreateObject("ADODB.Connection") CNN.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName Sql = " select 柜号,姓名,编号,类别,调出,备注,链接档案目录,日期 from [数据$] where 日期>=" & ComboBox5.Text & " and 日期<=" & ComboBox6.Text & "" If ComboBox1.Text <> "" Then Sql = Sql & "and 柜号 like '%" & ComboBox1.Text & "%'" If ComboBox2.Text <> "" Then Sql = Sql & "and 姓名 like '%" & ComboBox2.Text & "%'" If ComboBox3.Text <> "" Then Sql = Sql & "and 编号 like '%" & ComboBox3.Text & "%'" If ComboBox4.Text <> "" Then Sql = Sql & "and 类别 like '%" & ComboBox4.Text & "%'" RST.Open Sql, CNN, adOpenKeyset, adLockPessimistic Sheet2.[a2].CopyFromRecordset RST Set RST = Nothing Set CNN = Nothing Unload Me End Sub

[此贴子已经被作者于2006-4-15 16:04:33编辑过]

zbs112

SQL问题，盼指点，谢谢！

爱歌学习

增加一句： Set RST = CreateObject("Adodb.Recordset") 还有改这里： where 日期>=#" & ComboBox5.Text & "# and 日期<=#" & ComboBox6.Text & "#"

zbs112

1o3Ji6vE.rar (15.11 KB, 下载次数: 26)

2006-4-15 18:21 上传

点击文件名下载附件
[求助]多条件模糊查询的代码错在哪？

我按楼上的提示作了修改，可是还是调试不成功，请帮忙。

sandyw

加了＃格式后可以呀。

qee用

判断一下ComboBox5/ComboBox6的值后再加#吧.

zbs112

Private Sub CommandButton1_Click() Set CNN = CreateObject("ADODB.Connection") Set RST = CreateObject("ADODB.Recordset") CNN.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName Sql = "select 柜号,姓名,编号,类别,调出,备注,链接档案目录,日期 from [数据$] where 日期>=#" & ComboBox5.Text & "# and 日期<=#" & ComboBox6.Text & "#" If ComboBox1.Text <> "" Then Sql = Sql & "and 柜号 like '%" & ComboBox1.Text & "%'" If ComboBox2.Text <> "" Then Sql = Sql & "and 姓名 like '%" & ComboBox2.Text & "%'" If ComboBox3.Text <> "" Then Sql = Sql & "and 编号 like '%" & ComboBox3.Text & "%'" If ComboBox4.Text <> "" Then Sql = Sql & "and 类别 like '%" & ComboBox4.Text & "%'" RST.Open Sql, CNN, adOpenKeyset, adLockPessimistic Sheet2.[a2].CopyFromRecordset RST Set RST = Nothing Set CNN = Nothing Unload Me End Sub 提示的错误如下图片： 并提示这句出错：RST.Open Sql, CNN, adOpenKeyset, adLockPessimistic

来生缘

日期为空,肯定不行

用if 绕过去吧

cooloaky

1.判断日期是否为空

2.SQL里日期值需用#引用

3. and连接符前需有一个空格

If Len(ComboBox5.Text) = 0 Or Len(ComboBox6.Text) = 0 Then
MsgBox "起始或结束日期不能为空!"
Exit Sub
End If
Set CNN = CreateObject("ADODB.Connection")
CNN.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
Sql = " select 柜号,姓名,编号,类别,调出,备注,链接档案目录,日期 from [数据$] where 日期>=#" & ComboBox5.Text & "# and 日期<=#" & ComboBox6.Text & "#"
If ComboBox1.Text <> "" Then Sql = Sql & " and 柜号 like '%" & ComboBox1.Text & "%'"
If ComboBox2.Text <> "" Then Sql = Sql & " and 姓名 like '%" & ComboBox2.Text & "%'"
If ComboBox3.Text <> "" Then Sql = Sql & " and 编号 like '%" & ComboBox3.Text & "%'"
If ComboBox4.Text <> "" Then Sql = Sql & " and 类别 like '%" & ComboBox4.Text & "%'"
Set rst = CreateObject("adodb.recordset")
rst.Open Sql, CNN, adOpenKeyset, adLockPessimistic
Sheet2.[a2].CopyFromRecordset rst
Set rst = Nothing
Set CNN = Nothing
Unload Me

zbs112

谢谢楼上几位！我终于明白了。