機房收費系統之組合查詢

Private Sub cmdInquire_Click()

fgStuinfo.Clear   '每次查詢前要清空列表，以免重複    Dim mrc As ADODB.Recordset    Dim txtSQL As String    Dim msgText As String        Dim dok1 As Boolean    Dim dok2 As Boolean    Dim dok3 As Boolean            Dim Title(0 To 6) As String  '如果條件內容改變，只需要將此處中具體內容改變即可

'注意這裡的名稱為SQL表中索要查詢列的名稱    Title(0) = "cardno"    Title(1) = "name"    Title(2) = "ondate"    Title(3) = "ontime"    Title(4) = "offdate"    Title(5) = "offtime"    Title(6) = "consumemoney"        Dim compose(0 To 1) As String    compose(0) = " and "    compose(1) = " or "        On Error Resume Next        txtSQL = "select * from loginrecord_info where "    '第一行條件滿足    If Trim(comboFeild(0).Text) <> "" And combosign(0).Text <> "" And txtContent(0).Text <> "" Then        dok1 = True    Else        dok1 = False            End If        '第二行條件滿足    If comboFeild(1).Text <> "" And combosign(1).Text <> "" And txtContent(1).Text <> "" Then        dok2 = True    Else        dok2 = False    End If        '第三行條件滿足        If comboFeild(2).Text <> "" And combosign(2).Text <> "" And txtContent(2).Text <> "" Then        dok3 = True    Else        dok3 = False    End If        '一行條件也沒有    If (dok1 = False And dok2 = False And dok3 = False) Then        If comboFeild(0).Text = "" Then            MsgBox "請輸入欄位名！"            Exit Sub        Else            If combosign(0).Text = "" Then                MsgBox "請輸入操作符！"                Exit Sub            Else                If txtContent(0).Text = "" Then                    MsgBox "請輸入要查詢的內容！"                    Exit Sub                End If            End If        End If    End If            '有第一行條件    If dok1 = True Then        txtSQL = txtSQL & Title(comboFeild(0).ListIndex) & Trim(combosign(0).Text) & _            "'" & Trim(txtContent(0).Text) & "'"            End If        '有第二行條件    If dok2 = True Then        If dok1 = True Then            If ComboOperater(0).Text = "" Then                MsgBox "請選擇組合關係！"                Exit Sub            Else                txtSQL = txtSQL & compose(ComboOperater(0).ListIndex) & Title(comboFeild(1).ListIndex) & Trim(combosign(1).Text) & _                    "'" & Trim(txtContent(1).Text) & "'"            End If        Else            txtSQL = txtSQL & Title(comboFeild(1).ListIndex) & Trim(combosign(1).Text) & _                "'" & Trim(txtContent(1).Text) & "'"                        End If    End If            '有第三行條件       If dok3 = True Then        If dok2 = True Then            If dok1 = True Then                If ComboOperater(1).Text = "" Then                    MsgBox "請選擇第二個組合關係！"                    Exit Sub                Else                    txtSQL = txtSQL & compose(ComboOperater(1).ListIndex) & Title(comboFeild(2).ListIndex) & _                        combosign(2).Text & "'" & Trim(txtContent(2).Text) & "'"                                        End If            Else   ’如果dok1=false                txtSQL = txtSQL & compose(ComboOperater(1).ListIndex) & Title(comboFeild(2).ListIndex) & _                    combosign(2).Text & "'" & Trim(txtContent(2).Text) & "'"                            End If        Else            If dok1 = True Then                If ComboOperater(0).Text = "" Then                    MsgBox "請輸入第一個組合關係！"                    Exit Sub                Else   'dok2=true,dok3=true,dok1=false                    txtSQL = txtSQL & compose(ComboOperater(0).ListIndex) & Title(comboFeild(2).Text) & _                        combosign(2).Text & "'" & Trim(txtContent(2).Text) & "'"                End If            Else              txtSQL = txtSQL & Title(comboFeild(2).ListIndex) & combosign(2).Text & _                "'" & Trim(txtContent(2).Text) & "'"            End If           End If    End If            Set mrc = ExecuteSQL(txtSQL, msgText)        With fgStuinfo        .Rows = 2        .CellAlignment = 4        .TextMatrix(1, 0) = "卡號"        .TextMatrix(1, 1) = "姓名"        .TextMatrix(1, 2) = "登入日期"        .TextMatrix(1, 3) = "登入時間"        .TextMatrix(1, 4) = "下機日期"        .TextMatrix(1, 5) = "下機時間"        .TextMatrix(1, 6) = "消費金額"                Do While Not mrc.EOF            .Rows = .Rows + 1            .CellAlignment = 4            .TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(0))            .TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(2))            .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(5))            .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(6))            .TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(7))            .TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(8))            .TextMatrix(.Rows - 1, 6) = Trim(mrc.Fields(10))            mrc.MoveNext                    Loop                If .TextMatrix(2, 0) = "" Then '如果一條結果也沒有，進行提示！            MsgBox "沒有符合您想要的結果"            Exit Sub        End If            End WithEnd Sub

在這裡，我們還需注意listindex屬性的含義！它的返回值為數字，對應上面我們事先聲明的數組運算式！