机房收费系统中需要组合查询,如图
首先,我分析了查询的流程,并画了流程图,如下:
思路总结好了,下面就是代码的编写了(以学生上机统计信息为例):
Dim mrc As ADODB.Recordset Dim txtsql As String Dim Msgtxt As String If Combo3(0).Text = "" Then '不需要组合查询,第一行有为空值时提示错误信息 If Combo1(0).Text = "" Then MsgBox "请选择字段名",vbOKOnly + vbExclamation,"警告" Exit Sub Else If Combo2(0).Text = "" Then MsgBox "请选择操作符","警告" Exit Sub Else If Text1.Text = "" Then MsgBox "请填写要查询的内容","警告" Exit Sub Else '组合关系框为空且条件成立时,建立的查询,只有第一行" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' txtsql = "select * from line_info where " txtsql = txtsql & FName(Combo1(0).Text) & Trim(Combo2(0).Text) & "'" & Trim(Text1.Text) & "'" '''''''''''''''''''''''''''''''''''''''''''''''''''''''' Set mrc = Executesql(txtsql,Msgtxt) If Not (mrc.BOF And mrc.EOF) Then MSFlexGrid1.Rows = 1 MSFlexGrid1.CellAlignment = 4 MSFlexGrid1.TextMatrix(0,0) = "卡号" MSFlexGrid1.TextMatrix(0,1) = "姓名" MSFlexGrid1.TextMatrix(0,2) = "上机日期" MSFlexGrid1.TextMatrix(0,3) = "上机时间" MSFlexGrid1.TextMatrix(0,4) = "下机日期" MSFlexGrid1.TextMatrix(0,5) = "下机时间" MSFlexGrid1.TextMatrix(0,6) = "消费金额" MSFlexGrid1.TextMatrix(0,7) = "金额" MSFlexGrid1.TextMatrix(0,8) = "备注" Do While Not mrc.EOF '显示数据 With MSFlexGrid1 .Rows = .Rows + 1 '这种方式也可防止空白行的出现 .CellAlignment = 4 .TextMatrix(.Rows - 1,0) = mrc.Fields(1) .TextMatrix(.Rows - 1,1) = mrc.Fields(2) .TextMatrix(.Rows - 1,2) = Format(mrc.Fields(6),"yyyy-mm-dd") .TextMatrix(.Rows - 1,3) = mrc.Fields(7) .TextMatrix(.Rows - 1,4) = Format(mrc.Fields(8),5) = mrc.Fields(9) .TextMatrix(.Rows - 1,6) = mrc.Fields(11) .TextMatrix(.Rows - 1,7) = mrc.Fields(12) .TextMatrix(.Rows - 1,8) = mrc.Fields(13) mrc.MoveNext End With Loop Else MsgBox "该条件下没有记录!" End If End If End If End If End If If Not (Combo3(0).Text = "") Then 'combo3(0)不为空时,分两行或三行查询 If (Combo1(0).Text = "" Or Combo1(1).Text = "") Then MsgBox "请选择字段名","警告" '第一行与第二行都不能有空值,否则报错 Exit Sub Else If (Combo2(0).Text = "" Or Combo2(1).Text = "") Then MsgBox "请选择操作符","警告" Exit Sub Else If (Text1.Text = "" Or Text2.Text = "") Then MsgBox "请填写要查询的内容","警告" Exit Sub Else If Combo3(1).Text = "" Then 'combo3(1)为空值时,前两行查询 txtsql = "select * from line_info where " txtsql = txtsql & FName(Combo1(0).Text) & " " & Trim(Combo2(0).Text) & "'" & Trim(Text1.Text) & "'" & FName(Combo3(0).Text) & " " & FName(Combo1(1).Text) & Combo2(1).Text & "'" & Trim(Text2.Text) & "'" End If If Not Combo3(1).Text = "" Then 'combo3(1)不为空值时,三行查询 If (Combo1(2).Text = "") Then MsgBox "请选择条件3字段名","警告" '第一行与第二行都不能有空值,否则报错 Exit Sub Else If (Combo2(2).Text = "") Then MsgBox "请选择条件3操作符","警告" Exit Sub Else If (Text3.Text = "") Then MsgBox "请填写条件3要查询的内容","警告" Exit Sub Else '没有空值 txtsql = "select * from line_info where " txtsql = txtsql & FName(Combo1(0).Text) & Trim(Combo2(0).Text) & "'" & Trim(Text1.Text) & "'" & FName(Combo3(0).Text) & " " & FName(Combo1(1).Text) & Trim(Combo2(1).Text) & "'" & Trim(Text2.Text) & "' " & FName(Combo3(1).Text) & " " & FName(Combo1(2).Text) & Trim(Combo2(2).Text) & "'" & Trim(Text3.Text) & "'" End If End If End If End If End If End If End If End If Set mrc = Executesql(txtsql,7) = "余额" MSFlexGrid1.TextMatrix(0,8) = mrc.Fields(13) mrc.MoveNext End With Loop Else MsgBox "该条件下没有记录!" End If mrc.Close
逻辑不难,但是查询的时候我的思维有些问题,太麻烦了 ,在跟别人交流时,发现这种查询方法比我的简单多了,学到了很多,尤其是思维方式,自己应该积极转变,多多学习。
txtsql = "select * from line_info where " txtsql = txtsql & ...