以下是引用zzk386在2010-1-28 10:14:00的发言:还是有问题, 改过了。那个具体名称的字段你改了名称,所以会出错。 Private
Sub CommandButton2_Click() '主查询按钮 On
Error
Resume
Next Sheets("结果显示").Visible = 2 '隐藏"结果显示"工作表 Sheets("结果显示").Rows("2:65536") = "" '清空 CommandButton4.Caption = "显示查询结果" '建立ADO查询 Dim adoCN As
Object, i As
Integer Dim SQL As
String, strTJ As
String, Temp As
String Dim MySht As Worksheet Set adoCN = CreateObject("ADODB.Connection") '设定SQL If CheckBox1.Value = True
Then For
Each MySht In Worksheets If MySht.Name <> "结果显示" And MySht.Name <> "界面" _ And MySht.Name <> "帮助" And MySht.Name <> "引用" Then SQL = SQL & " Union all select """ & MySht.Name & """ as 项目表,* from [" & MySht.Name & "$]" End
If Next SQL = Right(SQL, Len(SQL) - 11) Else If ComboBox1.Value <> "" Then SQL = "select """ & ComboBox1.Value & """ as 项目表,* from [" & ComboBox1.Value & "$]" Else MsgBox "未选择表格" Exit
Sub End
If End
If '设定条件 For i = 3 To 5 Temp = UserForm2.Controls("Combobox" & i).Value If Len(Temp) > 0 Then strTJ = strTJ & " and " & UserForm2.Controls("Label" & i + 2).Caption & "=""" & Temp & """" End
If Next i '设定物品查询条件。如果没有具体名称则按照大类查找 If Len(ComboBox3.Text) = 0 And Len(ComboBox2.Text) > 0 Then '查找大类 Temp = ComboBox2.Text Dim stRow&, edRow&, strList stRow = Sheets("引用").Range("A:A").Find(Temp, Sheets("引用").Range("A2"), , , , xlNext).Row edRow = Sheets("引用").Range("A:A").Find("*", Sheets("引用").Range("A" & stRow), , , , xlNext).Row - 1 If edRow < stRow Then edRow = Sheets("引用").Range("B65536").End(xlUp).Row strList = Join(Application.Transpose(Sheets("引用").Range("B" & stRow & ":B" & edRow)), """,""") strTJ = strTJ & " and 名称和型号 in " & "(""" & strList & """)" End
If '设定日期条件 If CheckBox2.Value = True
Then '两个日期都要填写 If Len(TextBox1.Text) * Len(TextBox2.Text) > 0 Then strTJ = strTJ & " and 日期>=#" & TextBox1.Text & "# and 日期<=#" & TextBox2.Text & "#" Else MsgBox "日期没有填写完整" TextBox1.SetFocus Exit
Sub End
If End
If '若有条件,则添加条件 If Len(strTJ) > 0 Then SQL = "select * from (" & SQL & ") where " & Right(strTJ, Len(strTJ) - 5) End
If '打开连接 adoCN.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & ThisWorkbook.FullName & _ ";Extended Properties=Excel 8.0" Sheets("结果显示").Range("A2").CopyFromRecordset adoCN.Execute(SQL) '关闭连接 adoCN.Close '写入listbox1中,及各种显示 Call 列表框显示 Call 显示数值 If Len(ComboBox3.Text) = 0 Then
'如果具体名称复合框为空 Call 各种 Else Call 单位名称 End
If End
Sub |