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

来源：互联网发布：个人云计算编辑：程序博客网时间：2024/06/01 21:20

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

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

为简单起见给图中控件的命名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 WithEnd 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》

0 0