机房收费组合查询

  先说下刚开始敲组合查询的感受，敲一般用户里边的学生上机统计信息时我第一次就垮过去了，我是先把一般用户和操作员里边的非组合查询窗体敲完才开始反过头去敲一般用户里边的组合查询，刚开始代码弄不明白看别人博客里边的发现都写的不一样，有的写的稍微复杂，有的人写的也很简单。于是我就参考简单的一篇博客然后对比分析它是如何写的，它的逻辑是怎么走的。首先要搞明白组合查询窗体里边的每个控件都是干什么的，只有深入了解了它的作用才能明白他的用意，好对症下药。
  其实只要弄明白一个组合查询其他窗体真的是大同小异，只不过查的表不一样而已。组合查询就只是针对一个表来查的，不像上下机要用到好几个表。敲代码之前首先静下心来分析每个控件的功能，可以借鉴别人的博客，前提自己要理解，要着手画流程图，不画流程图逻辑会搞混的我吃过这样的亏，刚开始不会画流程图打开流程图软件不知道如何下手去画，没有自己的想法。行了机房也接近尾声了，流程图加代码总结如下：

流程图：

'判断第一行是否为空
    If Combo1(0) = "" Or Combo2(0) = "" Or Text4(0).Text = "" Then
        MsgBox "请将第一行条件填写完整!", vbOKOnly + vbExclamation, "提示"
        Exit Sub
    End If
        
    '查询上机表
    txtsql = "select * from Line_info where"
    '查询条件
    txtsql = txtsql & " " & Trim(field(Combo1(0).Text)) & Trim(Combo2(0).Text) & "'" & Trim(Text4(0).Text) & "'"
    
    If Trim(Combo3(0).Text <> "") Then
    
        If Combo1(0).Text = "" Or Combo2(0).Text = "" Or Text4(1).Text = "" Then
            MsgBox "你已经选择了第一个组合关系,请输入第二行组合条件。", vbOKOnly + vbExclamation, "提示"
            Exit Sub
        Else
            txtsql = txtsql & " " & field(Trim(Combo3(0).Text)) & " " & field(Combo1(1).Text) & Combo2(1).Text & "'" & Trim(Text4(1).Text) & "'"
        End If
     End If
        
     If Trim(Combo3(1).Text <> "") Then '第二个组合关系存在
        
        If Trim(Combo1(1).Text) = "" Or Trim(Combo2(1).Text) = "" Or Trim(Text4(2).Text) = "" Then
            MsgBox "你已经选择了第二个组合关系，请输入第三行查询条件!", vbOKOnly + vbExclamation, "提示"
            Exit Sub
        Else
            txtsql = txtsql & " " & field(Combo3(1).Text) & " " & field(Combo1(2).Text) & Combo2(2).Text & " ' " & Trim(Text4(2).Text) & " '"
        End If
    End If
    
    Set mrc = ExecuteSQL(txtsql, Msgtext)
    
    
    If mrc.EOF = True Then
        MsgBox "没有查询到结果!", , "提示"
        MSFlexGrid1.Clear
        Exit Sub
    End If
    '显示数据
    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) = Trim(mrc!studentname)
        .TextMatrix(.rows - 1, 2) = Trim(mrc!ondate)
        .TextMatrix(.rows - 1, 3) = Trim(mrc!OnTime)
        .TextMatrix(.rows - 1, 4) = Trim(mrc!offdate) 
        .TextMatrix(.rows - 1, 5) = Trim(mrc!offtime) 
        .TextMatrix(.rows - 1, 6) = Trim(mrc!consume) 
        .TextMatrix(.rows - 1, 7) = Trim(mrc!cash) 
        .TextMatrix(.rows - 1, 8) = Trim(mrc!Status)
        mrc.MoveNext
    Loop
    End With
    mrc.Close
End Sub


'不用添加引用导出Excel的代码
Private Sub Command3_Click()
    Dim xlApp As Object          '申明Object类对象 后期绑定
    Dim xlBook As Object        '
    Dim rows As Integer         '总行数
    Dim cols As Integer         '总列数
    Dim irow As Integer         '
    Dim hcol As Integer         '
    Dim icol As Integer         '
    
    If MSFlexGrid1.rows <= 1 Then                            '判断有无数据
        MsgBox "没有数据！", vbInformation, "提示"
    Else
        Set xlApp = CreateObject("Excel.Application")       '生成新的对象引用，引用Excel
        Set xlBook = xlApp.Workbooks.Add                    '创建空白的工作簿
        xlApp.Visible = True                                'Excel可见

        With MSFlexGrid1
            rows = .rows
            cols = .cols
            irow = 0
            icol = 1
            For hcol = 0 To cols - 1                        '列循环
                For irow = 1 To rows                        '行循环
                    xlApp.Cells(irow, icol).Value = .TextMatrix(irow - 1, hcol)     '将表中数据送到Excel
                Next irow
                icol = icol + 1
            Next hcol
        End With

        With xlApp
            .rows(1).Font.Bold = True                       '第一行为粗体
            .Cells.Select                                   '选择整个工作表
            .Columns.AutoFit                                '自动调整列宽以适应文字
            .Cells(1, 1).Select                             '
        End With

        xlApp.DisplayAlerts = False                         '关闭工作表，不提示用户保存
        Set xlApp = Nothing                                 '释放xlApp对象
        Set xlBook = Nothing                            '释放xlBook对象
    End If
End Sub

  模块里边需定一个函数，把vb里的字段、操作符转换成数据库里边可识别的。用时直接调用模块里边的就可以了。

'字段转换
Public Function field(comboField) As String
    Select Case Trim(comboField)
        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 = "cash"
        Case "备注"
            field = "status"
        Case "与"
            field = "and"
        Case "或"
            field = "or"
            

一直往前走才会发现别人发现不了的美景。。。