组合查询 ——vb

机房收费系统中需要组合查询，如图

首先，我分析了查询的流程，并画了流程图，如下：

思路总结好了，下面就是代码的编写了（以学生上机统计信息为例）：

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 & ...

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!