机房收费系统-组合查询

  机房收费系统中一个比较难理解的点-组合查询，组合查询窗体首先要理解逻辑关系，有很多种操作步骤及理解方法。理解此窗体的功能：通过选择字段名中的选择项及操作符来查询相应表格中的内容，并且可以利用组合查询的方式得到多个查询条件下的结果。并将结果导出为Excel形式。

  以学生上机统计信息为例：

窗体逻辑顺序（操作步骤）：

1.首先在字段名中有时间查询，在要查询的内容中设置一个关于时间选择的控件

2.字段名中有姓名等项目，在操作符中不能选择"<"或者">"

3.如果选择第一行则可以进行查询

4.如果选择第一个组合关系，判断前两行

5.如果选择第二个组合关系，判断所有

代码实现：

1.字段名

Function Field(i As String) As String
Select Case i
    Case "卡号"
        Field = "cardno"
    Case "姓名"
        Field = "studentname"
    Case "上机日期"
        Field = "ondate"
    Case "上机时间"
        Field = "ontime"
    Case "下机日期"
        Field = "offdate"
    Case "下机时间 "
        Field = "offtime"
    Case "消费金额"
        Field = "concume"
    Case "余额"
        Field = "cash"
    Case "备注"
        Field = "status"
        
    Case "或"
        Field = "or"
    Case "与"
        fiels = "and"
    End Select
End Function

2.查询

 '检查条件输入
    If Trim(Comfield1.Text) = "" Or Trim(Comperator1.Text) = "" Or Trim(Text1.Text) = "" Then
        MsgBox "请输入完整的查询条件", , "提示"
        Exit Sub
    End If
       
    '查询line_info表
    txtsql = "select * from line_info where"
    txtsql = txtsql & " " & Field(Comfield1.Text) & " " & Comperator1.Text & "'" & Trim(Text1.Text) & "'"
    '判断第一个组合关系是否选中
    If Trim(Comrelation1.Text <> "") Then
        '判断第二行内容是否填写完整，且符合要求
        If Trim(Comfield2.Text) = "" Or Trim(Comperator2.Text) = "" Or Trim(Text2.Text) = "" Then
            MsgBox "您选择了第一个组合关系，请输入第二行条件查询！", vbOKOnly + vbExclamation, "提示"
            Exit Sub
        Else
            txtsql = txtsql & " " & Field(Comrelation1.Text) & " " & Field(Comfield2.Text) & " " & Comperator2.Text & "'" & Trim(Text2.Text) & "'"
        End If
    End If
    
    '将前两行的条件联系起来，完成查询
    If Trim(Comrelation2.Text) <> "" Then
        '判断第二个组合关系是否选中
        If Trim(Comfield3.Text) = "" Or Trim(Comperator3.Text) = "" Or Trim(Text3.Text) = "" Then
            '如果选中，判断第二行选项内容是否填写完整
            MsgBox "您选择了第二个组合关系，请输入第三行条件查询！", vbOKOnly + vbExclamation, "提示"
            Exit Sub
        Else
            txtsql = txtsql & " " & Field(Comrelation2.Text) & " " & Field(Comfield3.Text) & " " & Trim(Comperator3.Text) & "'" & Trim(Text3.Text) & "'"
            '将三行信息联系起来
        End If
    End If

    Set mrc = ExecuteSQL(txtsql, msgtext)
    If mrc.EOF = True Then '检查信息是否存在，如果不存在给出提示并清空所有文本框
        MsgBox "没有查询到结果，可能会你输入的信息不存在，或者信息矛盾", vbOKOnly + vbExclamation, "警告"
        Comfield1.Text = ""
        Comfield2.Text = ""
        Comfield3.Text = ""
        Comperator1.Text = ""
        Comperator2.Text = ""
        Comperator3.Text = ""
        Text1.Text = ""
        Text2.Text = ""
        Text3.Text = ""
        Comrelation1.Text = ""
        Comrelation2.Text = ""
        Exit Sub
    Else
    
    With MSFlexGrid1
        .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) = Trim(mrc!cardno)
            .TextMatrix(.Rows - 1, 1) = mrc!studentname
            .TextMatrix(.Rows - 1, 2) = mrc!ondate
            .TextMatrix(.Rows - 1, 3) = mrc!OnTime
            .TextMatrix(.Rows - 1, 4) = mrc!offdate
            .TextMatrix(.Rows - 1, 5) = mrc!offtime
            .TextMatrix(.Rows - 1, 6) = mrc!consume
            .TextMatrix(.Rows - 1, 7) = mrc!cash
            .TextMatrix(.Rows - 1, 8) = mrc!Status
            mrc.MoveNext
    Loop
    mrc.Close
    End With
End If
End Sub

3.时间控件及操作符

Private Sub DTP1_Change()
'如果选择上下机日期
If Trim(Comfield1.Text) = "上机日期" Or Trim(Comfield1.Text) = "下机日期" Then
    Text1.Text = DTP1.Value
Else
    If Trim(Comfield1.Text) = "上机时间" Or Trim(Comfield1.Text) = "下机时间" Then
        '将dtpicker控件改为time格式，并可见
        DTP1.Format = 2 '默认为0，1为日期格式
        DTP1.Value = Time
        Text1.Text = DTP1.Value
    End If
End If
End Sub

Private Sub Comfield1_Click()
'如果选择上下机日期
If Trim(Comfield1.Text) = "上机日期" Or Trim(Comfield1.Text) = "下机日期" Or Trim(Comfield1.Text) = "上机时间" Or Trim(Comfield1.Text) = "下机时间" Then
    DTP1.Visible = True
Else
    DTP1.Visible = False
End If

'如果选择了姓名，则操作符为=或<>
If Trim(Comfield1.Text) = "姓名" Then
    '首先清空comperator控件
    Comperator1.Clear
    Comperator1.AddItem "="
    Comperator1.AddItem "<>"
Else
    '清空comperator控件
    Comperator1.Clear
    Comperator1.AddItem "="
    Comperator1.AddItem "<"
    Comperator1.AddItem ">"
    Comperator1.AddItem "<>"
End If
End Sub