机房收费系统问题集锦（二）——组合查询

在机房收费系统当中，有好几个窗体设计到了组合查询的问题，在这篇博客当中我就说一下组合查询的事

首先，组合查询的窗体如下所示：

为简单起见给图中控件的命名ComboBox控件的命名左边三列分别为combo（1，2，3），中间的为combo（4,5,6），右边的为（7,8）；文本框的命名就是Text（1,2,3）

查询语句如下所示：

Private Sub cmdCheck_Click()    

    Dim ctrl As Control
    Dim mrc As ADODB.Recordset
    Dim txtSQL As String
    Dim Msgtext As String
    Dim i As Integer
    
    If Combo7.Text = "" Then   
    
       If Trim(Text1.Text) = "" Or Trim(Combo1.Text) = "" Or Trim(Combo4.Text) = "" Then
          MsgBox "请输入完整的查询条件", , "提示"
          Exit Sub
       Else
          txtSQL = "select * from Line_info where " & Trim(field(Combo1.Text)) & Trim(Combo4.Text) & "'" & Trim(Text1.Text) & "'"
       End If
    Else
        
        If Combo8.Text = "" Then
           
           If Trim(Text2.Text) = "" Or Trim(Combo2.Text) = "" Or Trim(Combo5.Text) = "" Then
              MsgBox "请输入完整的查询条件", , "提示"
              Exit Sub
           Else
              txtSQL = "select * from Line_info where " & Trim(field(Combo1.Text)) & Trim(Combo4.Text) & "'" & Trim(Text1.Text) & "'" & " " & Trim(field(Combo7.Text)) & " " & Trim(field(Combo2.Text)) & Trim(Combo5.Text) & "'" & Trim(Text2.Text) & "'"
           End If
        
        Else
           
           If Trim(Text3.Text) = "" Or Trim(Combo3.Text) = "" Or Trim(Combo6.Text) = "" Then
              MsgBox "请输入完整的查询条件", , "提示"
              Exit Sub
           Else
              txtSQL = "select * from Line_info where " & Trim(field(Combo1.Text)) & Trim(Combo4.Text) & "'" & Trim(Text1.Text) & "'" & " " & Trim(field(Combo7.Text)) & " " & Trim(field(Combo2.Text)) & Trim(Combo5.Text) & "'" & Trim(field(Text2.Text)) & "'" & " " & Trim(field(Combo8.Text)) & " " & Trim(field(Combo3.Text)) & Trim(Combo6.Text) & "'" & Trim(Text3.Text) & "'"
           End If
        
        End If
    
    End If
           
    Set mrc = ExecuteSQL(txtSQL, Msgtext)
    If mrc.EOF = True Then '检查信息是否存在，如果不存在给出提示并清空所有文本框
        MsgBox "没有查询到结果，请检查输入信息！", vbOKOnly + vbExclamation, "提示"
        Call Cancel
        Exit Sub
    End If
    With MSHFlexGrid1
        .Rows = 1
        .TextMatrix(0, 0) = "卡号"
        .TextMatrix(0, 1) = "姓名"
        .TextMatrix(0, 2) = "上机日期"
        .TextMatrix(0, 3) = "上机时间"
        .TextMatrix(0, 4) = "下机日期"
        .TextMatrix(0, 5) = "下机时间"
        .TextMatrix(0, 6) = "消费金额"
        .TextMatrix(0, 7) = "余额"
        .TextMatrix(0, 8) = "备注"
          
     Do While Not mrc.EOF
            .Rows = .Rows + 1
            .TextMatrix(.Rows - 1, 0) = mrc.Fields(1).Value & ""
            .TextMatrix(.Rows - 1, 1) = mrc!studentname
            .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(6).Value & "")
            .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(7).Value & "")
            .TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(8).Value & "")
            .TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(9).Value & "")
            .TextMatrix(.Rows - 1, 6) = Trim(mrc.Fields(10).Value & "")
            .TextMatrix(.Rows - 1, 7) = Trim(mrc.Fields(11).Value & "")
            .TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(12).Value & "")
            mrc.MoveNext
        Loop
    End With
    mrc.Close

End Sub

语句虽然是看似简单，但是其中所含的信息无穷啊。在敲代码的时候千万不能忘记的空格的使用。

当然，大家也看到了在代码部分有一个调用“call cancel”，大家可能会说这是什么啊，没见过啊！

这是我为了方便书写而定义的一个过程，代码如下：

Public Function Cancel()
       Combo1.Text = ""
       Combo2.Text = ""
       Combo3.Text = ""
       Combo4.Text = ""
       Combo5.Text = ""
       Combo6.Text = ""
       Combo7.Text = ""
       Combo8.Text = ""
       Text1.Text = ""
       Text2.Text = ""
       Text3.Text = ""
    With MSHFlexGrid1
         .Rows = 2
         .CellAlignment = 4
         .TextMatrix(0, 0) = ""
         .TextMatrix(0, 1) = ""
         .TextMatrix(0, 2) = ""
         .TextMatrix(0, 3) = ""
         .TextMatrix(0, 4) = ""
         .TextMatrix(0, 5) = ""
         .TextMatrix(0, 6) = ""
         .TextMatrix(0, 7) = ""
         .TextMatrix(0, 8) = ""
         .TextMatrix(1, 0) = ""
         .TextMatrix(1, 1) = ""
         .TextMatrix(1, 2) = ""
         .TextMatrix(1, 3) = ""
         .TextMatrix(1, 4) = ""
         .TextMatrix(1, 5) = ""
         .TextMatrix(1, 6) = ""
         .TextMatrix(1, 7) = ""
         .TextMatrix(1, 8) = ""
    End With
End Function

这个过程主要是用于清空已填写的数据的

还有一个要说明的就是field过程，这个过程是为了方便我们查询数据库而存在的，定义过程如下：

Public Function field(comboX As String) As String
      Select Case comboX
            Case "卡号"
                field = "cardno"
            Case "姓名"
               field = "studentName"
            Case "上机日期"
               field = "ondate"
            Case "上机时间"
               field = "ontime"
            Case "下机日期"
               field = "offdate"
            Case "下机时间"
               field = "offtime"
            Case "消费金额"
               field = "consume"
            Case "余额"
               field = "cash"
            Case "备注"
               field = "status"
            Case "与"
               field = "and"
            Case "或"
               field = "or"
       End Select
    
End Function

这篇文章的内容就这么多了，就上图的“导出为Excel功能”请看《机房收费系统问题集锦（三）——导出为Excel》