机房收费系统之组合查询

       机房收费系统中，组合查询也是一个很难解决的问题。首先我们要理清思路，了解它的逻辑关系，才能更好的完成它，同时更要注重细节。

       组合查询，可分为三个阶段：
       1. 无组合关系，即只有第一层查询。
       2. 第一个组合关系，第一层和第二层查询。组合关系分为“与”和“或”关系。
       3. 第二组合关系，第一层，第二层和第三层。组合关系分为“与与”，“或或”，“与或”，“或与”关系。
      一些细节：”姓名”、“教师”、“机器名”只有等于和不等于，无大于和小于。

      “上机日期”和”上机时间”，作为条件查询时，要有提醒，正确输入日期或时间格式。

If Combo1.Text = "上机日期" Then
                    If Not IsDate(Text1.Text) Then
                    MsgBox "上机日期应输入日期格式（yyyy-mm-dd）！", vbOKOnly + vbExclamation, "提醒"
                  End If
                  End If
                If Combo1.Text = "上机时间" Then
                    If Not IsDate(Text1.Text) Then
                    MsgBox "上机时间应输入日期格式（yy:mm:dd）！", vbOKOnly + vbExclamation, "提醒"
                End If

       下面是组合查询代码：

Dim txtSQL As String '定义字符串变量，表示查询语句
    Dim Msgtext As String '定义字符串变量，返回查询语句
    Dim mrc As ADODB.Recordset '定义数据集对象
    '无组合关系，即只有第一层查询
    txtSQL = "select * from worklog_Info where "      
  
If Trim$(Combo7.Text) = "" Then                   
        '如果所选内容为空，则提示“请把您的输入项填写完整”
        '无组合关系，即只有第一层查询
        If Trim$(Combo1.Text) = "" Or Trim$(Combo4.Text) = "" Or Trim$(Text1.Text) = "" Then
            MsgBox "请把您的输入项填写完整", vbOKOnly + vbExclamation, "警告！"
            Exit Sub
        Else
            '执行sql语句
            txtSQL = txtSQL & Combo1.Text & Combo4.Text & "'" & Text1.Text & "'"
            Set mrc = ExecuteSQL(txtSQL, Msgtext)
  
            If mrc.EOF Then                     '数据库中无记录时
                MsgBox "无此记录", vbOKOnly + vbExclamation, "警告！"
                Combo1.SetFocus
                Text1.Text = ""
                mygrid1.Clear
                Exit Sub
            Else                                '数据库中有记录时
              With mygrid1
                .Rows = 2
                .CellAlignment = 4
                .TextMatrix(1, 0) = "教师"
                .TextMatrix(1, 1) = "注册日期"
                .TextMatrix(1, 2) = "注册时间"
                .TextMatrix(1, 3) = "注销日期"
                .TextMatrix(1, 4) = "注销时间"
                .TextMatrix(1, 5) = "机器名"
  
                Do While Not mrc.EOF
                    .Rows = .Rows + 1
                    .CellAlignment = 4
                    .TextMatrix(.Rows - 1, 0) = Trim$(mrc.Fields("UserID"))
                    .TextMatrix(.Rows - 1, 1) = Trim$(mrc.Fields("LoginDate"))
                    .TextMatrix(.Rows - 1, 2) = Trim$(mrc.Fields("LoginTime"))
                    .TextMatrix(.Rows - 1, 3) = Trim$(Format(mrc.Fields("LogoutDate")))
                    .TextMatrix(.Rows - 1, 4) = Trim$(Format(mrc.Fields("LogoutTime")))
                    .TextMatrix(.Rows - 1, 5) = Trim$(mrc.Fields("computer"))
  
                    mrc.MoveNext
  
                Loop
            End With
            mrc.Close
                
            End If
        End If
        Set mrc = Nothing                       '释放记录集
    End If
  
  
  
  '第二组合关系，第一层，第二层和第三层。组合关系分为“与与”，“或或”，“与或”，“或与”关系。

  
     txtSQL = "select * from worklog_Info where "      
  
    If Not Trim$(Combo7.Text) = "" Then
        '确保输入选项完整
        If Trim$(Combo1.Text) = "" Or Trim$(Combo4.Text) = "" Or Trim$(Text1.Text) = "" Or _
           Trim$(Combo2.Text) = "" Or Trim$(Combo5.Text) = "" Or Trim$(Text2.Text) = "" Then
            MsgBox "请把您的输入项填写完整", vbOKOnly + vbExclamation, "警告！"
            Exit Sub
        Else
  
            If Combo7.Text = "与" Then            '当Combo7为"与"时
                txtSQL = txtSQL & Combo1.Text & " " & Trim$(Combo4.Text) & "'" & _
                    Trim$(Text1.Text) & "'" & " " & "And" & " " & Combo2.Text & " " & _
                    Trim$(Combo5.Text) & "'" & Text2.Text & "'"
                Set mrc = ExecuteSQL(txtSQL, Msgtext)
  
                If mrc.EOF Then                      '数据库中无记录时
                    MsgBox "无此记录", vbOKOnly + vbExclamation, "警告！"
                    Combo2.SetFocus
                    Text2.Text = ""
                    mygrid1.Clear
                    Exit Sub
                Else
                    With mygrid1
                .Rows = 2
                .CellAlignment = 4
                .TextMatrix(1, 0) = "教师"
                .TextMatrix(1, 1) = "注册日期"
                .TextMatrix(1, 2) = "注册时间"
                .TextMatrix(1, 3) = "注销日期"
                .TextMatrix(1, 4) = "注销时间"
                .TextMatrix(1, 5) = "机器名"
  
                Do While Not mrc.EOF
                    .Rows = .Rows + 1
                    .CellAlignment = 4
                    .TextMatrix(.Rows - 1, 0) = Trim$(mrc.Fields("UserID"))
                    .TextMatrix(.Rows - 1, 1) = Trim$(mrc.Fields("LoginDate"))
                    .TextMatrix(.Rows - 1, 2) = Trim$(mrc.Fields("LoginTime"))
                    .TextMatrix(.Rows - 1, 3) = Trim$(Format(mrc.Fields("LogoutDate")))
                    .TextMatrix(.Rows - 1, 4) = Trim$(Format(mrc.Fields("LogoutTime")))
                    .TextMatrix(.Rows - 1, 5) = Trim$(mrc.Fields("computer"))
  
                    mrc.MoveNext
  
                Loop
            End With
            mrc.Close

                End If
            Else
                '当Combo7为"或"时
                txtSQL = txtSQL & Combo1.Text & " " & Trim$(Combo4.Text) & "'" & _
                  Trim$(Text1.Text) & "'" & " " & "or" & " " & Combo2.Text& " " & _
                  Trim$(Combo5.Text) & "'" & Text2.Text & "'"
                Set mrc = ExecuteSQL(txtSQL, Msgtext)
  
                If mrc.EOF Then                     '数据库中无记录时
                    MsgBox "无此记录", vbOKOnly + vbExclamation, "警告！"
                    Combo2.SetFocus
                    Text2.Text = ""
                    mygrid1.Clear
                    Exit Sub
                Else
                    With mygrid1
                .Rows = 2
                .CellAlignment = 4
                .TextMatrix(1, 0) = "教师"
                .TextMatrix(1, 1) = "注册日期"
                .TextMatrix(1, 2) = "注册时间"
                .TextMatrix(1, 3) = "注销日期"
                .TextMatrix(1, 4) = "注销时间"
                .TextMatrix(1, 5) = "机器名"
  
                Do While Not mrc.EOF
                    .Rows = .Rows + 1
                    .CellAlignment = 4
                    .TextMatrix(.Rows - 1, 0) = Trim$(mrc.Fields("UserID"))
                    .TextMatrix(.Rows - 1, 1) = Trim$(mrc.Fields("LoginDate"))
                    .TextMatrix(.Rows - 1, 2) = Trim$(mrc.Fields("LoginTime"))
                    .TextMatrix(.Rows - 1, 3) = Trim$(Format(mrc.Fields("LogoutDate")))
                    .TextMatrix(.Rows - 1, 4) = Trim$(Format(mrc.Fields("LogoutTime")))
                    .TextMatrix(.Rows - 1, 5) = Trim$(mrc.Fields("computer"))
  
                    mrc.MoveNext
  
                Loop
            End With
            mrc.Close
                '调用添加函数，把数据载入到flexgrid控件中
                End If
            End If
        End If
         Set mrc = Nothing                          '释放记录集
    End If
  
  
  
  
   '第二组合关系，第一层，第二层和第三层。组合关系分为“与与”，“或或”，“与或”，“或与”关系。

  
txtSQL = "select * from worklog_Info where "     
  
   If Not Trim$(Combo8.Text) = "" Then
        '确保输入选项完整
        If Trim$(Combo1.Text) = "" Or Trim$(Combo4.Text) = "" Or Trim$(Text1.Text) = "" Or _
            Trim$(Combo2.Text) = "" Or Trim$(Combo5.Text) = "" Or Trim$(Text2.Text) = "" _
            Or Trim(Combo3.Text) = "" Or Trim(Combo6.Text) = "" Or Trim(Text3.Text) = "" Then
            MsgBox "请把您的输入项填写完整", vbOKOnly + vbExclamation, "警告！"
            Exit Sub
        Else
  
           '两个组合关系均为“与”时
           If Combo7.Text = "与" And Combo8.Text = "与" Then
                txtSQL = txtSQL & Combo1.Text & " " & Trim$(Combo4.Text) & _"'" & Trim$(Text1.Text) & "'" & " " & "And" & " " & Combo2.Text _  & " " & Trim$(Combo5.Text) & "'" & Text2.Text & "'" & " " & "And" _& " " & Combo3.Text & " " & Trim(Combo6.Text) & "'" & Trim(Text3.Text) & "'"
                Set mrc = ExecuteSQL(txtSQL, Msgtext)
  
                If mrc.EOF Then                      '数据库中无记录时
                    MsgBox "无此记录", vbOKOnly + vbExclamation, "警告！"
                    Combo3.SetFocus
                    Text3.Text = ""
                    mygrid1.Clear
                    Exit Sub
                Else
                    With mygrid1
                .Rows = 2
                .CellAlignment = 4
                .TextMatrix(1, 0) = "教师"
                .TextMatrix(1, 1) = "注册日期"
                .TextMatrix(1, 2) = "注册时间"
                .TextMatrix(1, 3) = "注销日期"
                .TextMatrix(1, 4) = "注销时间"
                .TextMatrix(1, 5) = "机器名"
  
                Do While Not mrc.EOF
                    .Rows = .Rows + 1
                    .CellAlignment = 4
                    .TextMatrix(.Rows - 1, 0) = Trim$(mrc.Fields("UserID"))
                    .TextMatrix(.Rows - 1, 1) = Trim$(mrc.Fields("LoginDate"))
                    .TextMatrix(.Rows - 1, 2) = Trim$(mrc.Fields("LoginTime"))
                    .TextMatrix(.Rows - 1, 3) = Trim$(Format(mrc.Fields("LogoutDate")))
                    .TextMatrix(.Rows - 1, 4) = Trim$(Format(mrc.Fields("LogoutTime")))
                    .TextMatrix(.Rows - 1, 5) = Trim$(mrc.Fields("computer"))
  
                    mrc.MoveNext
  
                Loop
            End With
            mrc.Close

                End If
            End If
  
  
           '第一个组合关系为“与”，第二个组合关系为“或”
  
           If Combo7.Text = "与" And Combo8.Text = "或" Then
                txtSQL = txtSQL &Combo1.Text & " " & Trim$(Combo4.Text) & "'" & _
                    Trim$(Text1.Text) & "'" & " " & "And" & " " &Combo2.Text & _
                    " " & Trim$(Combo5.Text) & "'" & Text2.Text & "'" & " " & "or" & " " & _
                   Combo3.Text & " " & Trim(Combo6.Text) & "'" & Trim(Text3.Text) & "'"
                Set mrc = ExecuteSQL(txtSQL, Msgtext)
  
                If mrc.EOF Then                      '数据库中无记录时
                    MsgBox "无此记录", vbOKOnly + vbExclamation, "警告！"
                    Combo3.SetFocus
                    Text3.Text = ""
                    mygrid1.Clear
                    Exit Sub
                Else
                    With mygrid1
                .Rows = 2
                .CellAlignment = 4
                .TextMatrix(1, 0) = "教师"
                .TextMatrix(1, 1) = "注册日期"
                .TextMatrix(1, 2) = "注册时间"
                .TextMatrix(1, 3) = "注销日期"
                .TextMatrix(1, 4) = "注销时间"
                .TextMatrix(1, 5) = "机器名"
  
                Do While Not mrc.EOF
                    .Rows = .Rows + 1
                    .CellAlignment = 4
                    .TextMatrix(.Rows - 1, 0) = Trim$(mrc.Fields("UserID"))
                    .TextMatrix(.Rows - 1, 1) = Trim$(mrc.Fields("LoginDate"))
                    .TextMatrix(.Rows - 1, 2) = Trim$(mrc.Fields("LoginTime"))
                    .TextMatrix(.Rows - 1, 3) = Trim$(Format(mrc.Fields("LogoutDate")))
                    .TextMatrix(.Rows - 1, 4) = Trim$(Format(mrc.Fields("LogoutTime")))
                    .TextMatrix(.Rows - 1, 5) = Trim$(mrc.Fields("computer"))
  
                    mrc.MoveNext
  
                Loop
            End With
            mrc.Close

                End If
            End If
  
  
            '第一个组合关系为“或”，第二个组合关系为“与”
  
  
            If Combo7.Text = "或" And Combo8.Text = "与" Then
                txtSQL = txtSQL &Combo1.Text& " " & Trim$(Combo4.Text) & "'" _
                        & Trim$(Text1.Text) & "'" & " " & "or" & " " & Combo2.Text_
                        & " " & Trim$(Combo5.Text) & "'" & Text2.Text & "'" & " " & "and" & " " _
                        & Combo3.Text & " " & Trim(Combo6.Text) & "'" & Trim(Text3.Text) & "'"
                Set mrc = ExecuteSQL(txtSQL, Msgtext)
  
                If mrc.EOF Then                      '数据库中无记录时
                    MsgBox "无此记录", vbOKOnly + vbExclamation, "警告！"
                    Combo3.SetFocus
                    Text3.Text = ""
                    mygrid1.Clear
                    Exit Sub
                Else
                    With mygrid1
                .Rows = 2
                .CellAlignment = 4
                .TextMatrix(1, 0) = "教师"
                .TextMatrix(1, 1) = "注册日期"
                .TextMatrix(1, 2) = "注册时间"
                .TextMatrix(1, 3) = "注销日期"
                .TextMatrix(1, 4) = "注销时间"
                .TextMatrix(1, 5) = "机器名"
  
                Do While Not mrc.EOF
                    .Rows = .Rows + 1
                    .CellAlignment = 4
                    .TextMatrix(.Rows - 1, 0) = Trim$(mrc.Fields("UserID"))
                    .TextMatrix(.Rows - 1, 1) = Trim$(mrc.Fields("LoginDate"))
                    .TextMatrix(.Rows - 1, 2) = Trim$(mrc.Fields("LoginTime"))
                    .TextMatrix(.Rows - 1, 3) = Trim$(Format(mrc.Fields("LogoutDate")))
                    .TextMatrix(.Rows - 1, 4) = Trim$(Format(mrc.Fields("LogoutTime")))
                    .TextMatrix(.Rows - 1, 5) = Trim$(mrc.Fields("computer"))
                    mrc.MoveNext
  
                Loop
            End With
            mrc.Close

                End If
            End If
  
  
            '第一个组合关系为“或”，第二个组合关系为“或”
  
  
            If Combo7.Text = "或" And Combo8.Text = "与" Then
                txtSQL = txtSQL & Combo1.Text & " " & Trim$(Combo4.Text) & "'" _
                        & Trim$(Text1.Text) & "'" & " " & "or" & " " & Combo2.Text _
                        & " " & Trim$(Combo5.Text) & "'" & Text2.Text & "'" & " " & "or" & " " _
                        & Combo3.Text & " " & Trim(Combo6.Text) & "'" & Trim(Text3.Text) & "'"
                Set mrc = ExecuteSQL(txtSQL, Msgtext)
  
                If mrc.EOF Then                      '数据库中无记录时
                    MsgBox "无此记录", vbOKOnly + vbExclamation, "警告！"
                    Combo3.SetFocus
                    Text3.Text = ""
                    mygrid1.Clear
                    Exit Sub
                Else
                    With mygrid1
                .Rows = 2
                .CellAlignment = 4
                .TextMatrix(1, 0) = "教师"
                .TextMatrix(1, 1) = "注册日期"
                .TextMatrix(1, 2) = "注册时间"
                .TextMatrix(1, 3) = "注销日期"
                .TextMatrix(1, 4) = "注销时间"
                .TextMatrix(1, 5) = "机器名"
  
                Do While Not mrc.EOF
                    .Rows = .Rows + 1
                    .CellAlignment = 4
                    .TextMatrix(.Rows - 1, 0) = Trim$(mrc.Fields("UserID"))
                    .TextMatrix(.Rows - 1, 1) = Trim$(mrc.Fields("LoginDate"))
                    .TextMatrix(.Rows - 1, 2) = Trim$(mrc.Fields("LoginTime"))
                    .TextMatrix(.Rows - 1, 3) = Trim$(Format(mrc.Fields("LogoutDate")))
                    .TextMatrix(.Rows - 1, 4) = Trim$(Format(mrc.Fields("LogoutTime")))
                    .TextMatrix(.Rows - 1, 5) = Trim$(mrc.Fields("computer"))
  
                    mrc.MoveNext
  
                Loop
            End With
            mrc.Close

                End If
            End If
  
          Set mrc = Nothing                          '释放记录集
      End If
    End If