【机房】组合查询--学生上机信息统计

机房收费系统中的操作员可以统计学生上机信息，这里用到了组合查询

窗体界面：

字段名、操作符和组合关系的定义：

Private Sub Form_Load()  
    Dim i, j, k, o As Integer
    For i = 0 To 2
        comboField(i).AddItem "卡号"
        comboField(i).AddItem "姓名"
        comboField(i).AddItem "上机日期"
        comboField(i).AddItem "下机日期"
        comboField(i).AddItem "消费金额"
        comboField(i).AddItem "余额"        
    Next    
    For j = 0 To 2
        comboOpSign(j).AddItem "="
        comboOpSign(j).AddItem ">"
        comboOpSign(j).AddItem "<"
        comboOpSign(j).AddItem "<>"        
    Next    
    For k = 0 To 1
        comboCombRela(k).AddItem "与"
        comboCombRela(k).AddItem "或"
    Next
End Sub

点击清空记录

Private Sub cmdClear_Click()    
    Dim ctr1 As Control     
    For Each ctr1 In Me.Controls
        If TypeOf ctr1 Is TextBox Then '判断是否是文本框
        ctr1.Text = ""
        End If
    Next    
    For Each ctr1 In Me.Controls
        If TypeOf ctr1 Is ComboBox Then '判断是否是文本框
        ctr1.Text = ""
        End If
    Next   
    MSHFlexGrid1.Rows = 1    
End Sub

查询

查询的时候需要先判断是否为空

 '判断字段名是否为空
    If Trim(comboField(0)) = "" Then
        MsgBox "请选择字段名", 64, "温馨提示"
        comboField(0).SetFocus
        Exit Sub
    End If

然后当有组合关系的时候，还需要判断下一个查询条件是否为空

Rem;若是选择组合关系
    If Trim(comboCombRela(0)) <> "" Then
        
             '判断字段名是否为空
        If Trim(comboField(1)) = "" Then
            MsgBox "请选择字段名", 64, "温馨提示"
            comboField(1).SetFocus
            Exit Sub
        End If
            
        '判断操作符是否为空
        If Trim(comboOpSign(1)) = "" Then
            MsgBox "请选择操作符", 64, "温馨提示"
            comboOpSign(1).SetFocus
            Exit Sub
        End If
        
        '判断操作符是否为空
        If Trim(txtInquiryCont2) = "" Then
            MsgBox "请输入要查询的内容", 64, "温馨提示"
            txtInquiryCont2.SetFocus
            Exit Sub
        End If
    
    End If

组合查询的语句有些长，可以分开写，注意空格是否存在。

Rem:连接数据库
    txtSQL = "select * from Line_info where"
    
    '与上一行SQL语句组合
    txtSQL = txtSQL & " " & Trim(field(comboField(0).Text)) & " " & _
        Trim((comboOpSign(0).Text)) & " " & "'" & _
        Trim(txtInquiryCont1.Text) & "'"
        

下面判断组合关系存在时，是否有记录

 ' 第一个组合关系存在
        If Trim(comboCombRela(0).Text <> "") Then
             txtSQL = txtSQL & " " & field(Trim(comboCombRela(0).Text)) & " " & field(comboField(1).Text) & " " & comboOpSign(1).Text & " " & "'" & Trim(txtInquiryCont2.Text) & "'"
        End If

        '第二个组合关系存在
        If Trim(comboCombRela(1).Text <> "") Then
            txtSQL = txtSQL & "" & field(comboCombRela(1).Text) & "" & Trim(field(comboField(2).Text)) & "" & _
                Trim(comboOpSign(2).Text) & "" & "'" & _
                Trim(txtInquiryCont3.Text) & "'"
        End If

若没有结果，还需要重新输入，若存在结果，则需要打印到MSHFlexGrid表格中。

 Set mrc = ExecuteSQL(txtSQL, MsgText)
        
    If mrc.EOF = True Then
        MsgBox "没有查询到结果,请重新输入", 64, "温馨提示"
        Exit Sub
    Else
         With MSHFlexGrid1
            .Rows = 1
            .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) = "余额"
            
        Do While Not mrc.EOF
            .Rows = .Rows + 1
            .CellAlignment = 4
            .TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(1))         '卡号
            .TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(3))         '姓名
            .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(6))         '上机日期
            .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(7))         '上机时间
            .TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(8)) & ""    '下机日期
            .TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(9)) & ""    '下机时间
            .TextMatrix(.Rows - 1, 6) = Trim(mrc.Fields(11))         '消费金额
            .TextMatrix(.Rows - 1, 7) = Trim(mrc.Fields(12))         '余额        
            mrc.MoveNext
        Loop
        End With
    End If

欢迎斧正~