组合查询——vb

     机房收费系统中需要组合查询&＃xff0c;如图

     首先&＃xff0c;我分析了查询的流程&＃xff0c;并画了流程图&＃xff0c;如下&＃xff1a;

思路总结好了&＃xff0c;下面就是代码的编写了&＃xff08;以学生上机统计信息为例&＃xff09;&＃xff1a;

Dim mrc As ADODB.Recordset
Dim txtSQL As String
Dim Msgtxt As StringIf Combo3(0).Text &＃61; "" Then &＃39;不需要组合查询&＃xff0c;第一行有为空值时提示错误信息If Combo1(0).Text &＃61; "" ThenMsgBox "请选择字段名", vbOKOnly &＃43; vbExclamation, "警告"Exit SubElseIf Combo2(0).Text &＃61; "" ThenMsgBox "请选择操作符", vbOKOnly &＃43; vbExclamation, "警告"Exit SubElseIf Text1.Text &＃61; "" ThenMsgBox "请填写要查询的内容", vbOKOnly &＃43; vbExclamation, "警告"Exit SubElse &＃39;组合关系框为空且条件成立时&＃xff0c;建立的查询&＃xff0c;只有第一行"&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;txtSQL &＃61; "select * from line_info where "txtSQL &＃61; txtSQL & FName(Combo1(0).Text) & Trim(Combo2(0).Text) & "&＃39;" & Trim(Text1.Text) & "&＃39;"&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;Set mrc &＃61; ExecuteSQL(txtSQL, Msgtxt)If Not (mrc.BOF And mrc.EOF) ThenMSFlexGrid1.Rows &＃61; 1MSFlexGrid1.CellAlignment &＃61; 4MSFlexGrid1.TextMatrix(0, 0) &＃61; "卡号"MSFlexGrid1.TextMatrix(0, 1) &＃61; "姓名"MSFlexGrid1.TextMatrix(0, 2) &＃61; "上机日期"MSFlexGrid1.TextMatrix(0, 3) &＃61; "上机时间"MSFlexGrid1.TextMatrix(0, 4) &＃61; "下机日期"MSFlexGrid1.TextMatrix(0, 5) &＃61; "下机时间"MSFlexGrid1.TextMatrix(0, 6) &＃61; "消费金额"MSFlexGrid1.TextMatrix(0, 7) &＃61; "金额"MSFlexGrid1.TextMatrix(0, 8) &＃61; "备注"Do While Not mrc.EOF &＃39;显示数据With MSFlexGrid1.Rows &＃61; .Rows &＃43; 1 &＃39;这种方式也可防止空白行的出现.CellAlignment &＃61; 4.TextMatrix(.Rows - 1, 0) &＃61; mrc.Fields(1).TextMatrix(.Rows - 1, 1) &＃61; mrc.Fields(2).TextMatrix(.Rows - 1, 2) &＃61; Format(mrc.Fields(6), "yyyy-mm-dd").TextMatrix(.Rows - 1, 3) &＃61; mrc.Fields(7).TextMatrix(.Rows - 1, 4) &＃61; Format(mrc.Fields(8), "yyyy-mm-dd").TextMatrix(.Rows - 1, 5) &＃61; mrc.Fields(9).TextMatrix(.Rows - 1, 6) &＃61; mrc.Fields(11).TextMatrix(.Rows - 1, 7) &＃61; mrc.Fields(12).TextMatrix(.Rows - 1, 8) &＃61; mrc.Fields(13)mrc.MoveNextEnd WithLoopElseMsgBox "该条件下没有记录&＃xff01;"End IfEnd IfEnd IfEnd IfEnd IfIf Not (Combo3(0).Text &＃61; "") Then &＃39;combo3&＃xff08;0&＃xff09;不为空时&＃xff0c;分两行或三行查询If (Combo1(0).Text &＃61; "" Or Combo1(1).Text &＃61; "") ThenMsgBox "请选择字段名", vbOKOnly &＃43; vbExclamation, "警告" &＃39;第一行与第二行都不能有空值&＃xff0c;否则报错Exit SubElseIf (Combo2(0).Text &＃61; "" Or Combo2(1).Text &＃61; "") ThenMsgBox "请选择操作符", vbOKOnly &＃43; vbExclamation, "警告"Exit SubElseIf (Text1.Text &＃61; "" Or Text2.Text &＃61; "") ThenMsgBox "请填写要查询的内容", vbOKOnly &＃43; vbExclamation, "警告"Exit SubElseIf Combo3(1).Text &＃61; "" Then &＃39;combo3&＃xff08;1&＃xff09;为空值时&＃xff0c;前两行查询txtSQL &＃61; "select * from line_info where "txtSQL &＃61; txtSQL & FName(Combo1(0).Text) & " " & Trim(Combo2(0).Text) & "&＃39;" & Trim(Text1.Text) & "&＃39;" & FName(Combo3(0).Text) & " " & FName(Combo1(1).Text) & Combo2(1).Text & "&＃39;" & Trim(Text2.Text) & "&＃39;"End IfIf Not Combo3(1).Text &＃61; "" Then &＃39;combo3&＃xff08;1&＃xff09;不为空值时&＃xff0c;三行查询If (Combo1(2).Text &＃61; "") ThenMsgBox "请选择条件3字段名", vbOKOnly &＃43; vbExclamation, "警告" &＃39;第一行与第二行都不能有空值&＃xff0c;否则报错Exit SubElseIf (Combo2(2).Text &＃61; "") ThenMsgBox "请选择条件3操作符", vbOKOnly &＃43; vbExclamation, "警告"Exit SubElseIf (Text3.Text &＃61; "") ThenMsgBox "请填写条件3要查询的内容", vbOKOnly &＃43; vbExclamation, "警告"Exit SubElse &＃39;没有空值txtSQL &＃61; "select * from line_info where "txtSQL &＃61; txtSQL & FName(Combo1(0).Text) & Trim(Combo2(0).Text) & "&＃39;" & Trim(Text1.Text) & "&＃39;" & FName(Combo3(0).Text) & " " & FName(Combo1(1).Text) & Trim(Combo2(1).Text) & "&＃39;" & Trim(Text2.Text) & "&＃39; " & FName(Combo3(1).Text) & " " & FName(Combo1(2).Text) & Trim(Combo2(2).Text) & "&＃39;" & Trim(Text3.Text) & "&＃39;"End IfEnd IfEnd IfEnd IfEnd IfEnd IfEnd IfEnd IfSet mrc &＃61; ExecuteSQL(txtSQL, Msgtxt)If Not (mrc.BOF And mrc.EOF) ThenMSFlexGrid1.Rows &＃61; 1MSFlexGrid1.CellAlignment &＃61; 4MSFlexGrid1.TextMatrix(0, 0) &＃61; "卡号"MSFlexGrid1.TextMatrix(0, 1) &＃61; "姓名"MSFlexGrid1.TextMatrix(0, 2) &＃61; "上机日期"MSFlexGrid1.TextMatrix(0, 3) &＃61; "上机时间"MSFlexGrid1.TextMatrix(0, 4) &＃61; "下机日期"MSFlexGrid1.TextMatrix(0, 5) &＃61; "下机时间"MSFlexGrid1.TextMatrix(0, 6) &＃61; "消费金额"MSFlexGrid1.TextMatrix(0, 7) &＃61; "余额"MSFlexGrid1.TextMatrix(0, 8) &＃61; "备注"Do While Not mrc.EOF &＃39;显示数据With MSFlexGrid1.Rows &＃61; .Rows &＃43; 1 &＃39;这种方式也可防止空白行的出现.CellAlignment &＃61; 4.TextMatrix(.Rows - 1, 0) &＃61; mrc.Fields(1).TextMatrix(.Rows - 1, 1) &＃61; mrc.Fields(2).TextMatrix(.Rows - 1, 2) &＃61; Format(mrc.Fields(6), "yyyy-mm-dd").TextMatrix(.Rows - 1, 3) &＃61; mrc.Fields(7).TextMatrix(.Rows - 1, 4) &＃61; Format(mrc.Fields(8), "yyyy-mm-dd").TextMatrix(.Rows - 1, 5) &＃61; mrc.Fields(9).TextMatrix(.Rows - 1, 6) &＃61; mrc.Fields(11).TextMatrix(.Rows - 1, 7) &＃61; mrc.Fields(12).TextMatrix(.Rows - 1, 8) &＃61; mrc.Fields(13)mrc.MoveNextEnd WithLoopElseMsgBox "该条件下没有记录&＃xff01;"End Ifmrc.Close

       逻辑不难&＃xff0c;但是查询的时候我的思维有些问题&＃xff0c;太麻烦了 &＃xff0c;在跟别人交流时&＃xff0c;发现这种查询方法比我的简单多了&＃xff0c;学到了很多&＃xff0c;尤其是思维方式&＃xff0c;自己应该积极转变&＃xff0c;多多学习。

txtSQL &＃61; "select * from line_info where "txtSQL &＃61; txtSQL & ...