如何实现像excel一样多条件筛选功能
各位大师,菜菜向大家求教,如何实现像excel一样多条件筛选功能?并将结果显示在listreview中,请大家指点,谢谢。
见附件!
请教.rar
(66.91 KB)
[ 本帖最后由 yuk_yu 于 2010-3-15 09:31 编辑 ]
'SQL 命令中,关键字\字段名\值 之间多个空格更不容易出现错误. dim stmp as string if 条件1 then '如果选择了第一个条件 stmp=stmp & " and 条件1 =" & 条件1 end if if 条件2 then '如果选择了第二个条件 stmp=stmp & " and 条件2 =" & 条件2 end if if 条件3 then '如果选择了第三个条件 stmp=stmp & " and 条件3 =" & 条件3 end if ............ '有几组写几组,如果条件控件是数组,那么用循环也可以.按此类似结果写 '最后生成的结果是: and 条件1=条件1 and 条件2=条件2 ... if len(stmp)>0 then '选择过了条件 stmp="where " & mid( stmp ,5) '干掉最前面那个 and ,然后再加上条件头 end if sql= sql1 & stmp 'sql1 为原始,没有筛选条件的全部查询的 SQL 命令,然后再加上筛选条件形成 含筛选条件的 SQL 命令
Private Sub Combo1_Click(Index As Integer) xztj = True Dim stmp As String Dim i As Long For i = 0 To Combo1.Count - 1 If Combo1(i).ListIndex > 0 Then stmp = stmp & " and [" & Trim(Label1(i).Caption) & "] = " & fam(Combo1(i).Text, IIf(i = 1, 2, 1)) ' End If Next i If Len(stmp) > 0 Then stmp = " where " & Mid(stmp, 5) End If SQL = "select * from CTSReport" & stmp Text2.Text = SQL 'xztj = True 'If Index = 0 Then 'SQL = "select * from CTSReport where [" & Trim(Label1(0).Caption) & "] = """ & Combo1(0).Text & """" 'ElseIf Index = 1 Then 'SQL = "select * from CTSReport where [" & Trim(Label1(1).Caption) & "] = #" & Combo1(1).Text & "#" 'ElseIf Index = 2 Then 'SQL = "select * from CTSReport where [" & Trim(Label1(2).Caption) & "] = """ & Combo1(2).Text & """" 'ElseIf Index = 3 Then 'SQL = "select * from CTSReport where [" & Trim(Label1(3).Caption) & "] = """ & Combo1(3).Text & """" 'ElseIf Index = 4 Then 'SQL = "select * from CTSReport where [" & Trim(Label1(4).Caption) & "] = """ & Combo1(4).Text & """" 'End If 'Text2.Text = SQL End Sub Private Function fam(cs As String, tt As Long) As String '根据传入 的类型,加引导符 'tt =1 加引号 ,=2 加 # Dim bb As String Select Case tt Case 1 bb = """" Case 2 bb = "#" End Select fam = bb & cs & bb End Function
Private Sub Form_Load() Dim Dname As String Dname = App.Path If Right(Dname, 1) <> "\" Then Dname = Dname & "\" Dname = Dname & "CTS.mdb" Cnstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Dname & " ;Jet OLEDB:Database" '---------数据库连接 For i = 0 To 4 Combo1(i).AddItem "--空--" '增加一项无条件 Call Addcombox(Combo1(i), Trim(Label1(i))) Combo1(i).Text = Combo1(i).List(0) Next i cn.Open Cnstr S = "select * from CTSReport" rs.Open S, cn, 1, 1 For i = 0 To rs.Fields.Count - 1 ListView1.ColumnHeaders.Add = rs.Fields.Item(i).Name Next i rs.Close cn.Close End Sub