机房收费系统-组合查询

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

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

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

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

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

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

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

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

代码实现：

1.字段名

Function Field(i As String) As StringSelect 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 SelectEnd 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 WithEnd IfEnd Sub

3.时间控件及操作符

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

Private Sub Comfield1_Click()'如果选择上下机日期If Trim(Comfield1.Text) = "上机日期" Or Trim(Comfield1.Text) = "下机日期" Or Trim(Comfield1.Text) = "上机时间" Or Trim(Comfield1.Text) = "下机时间" Then    DTP1.Visible = TrueElse    DTP1.Visible = FalseEnd 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 IfEnd Sub