机房收费系统——组合查询

前言

组合查询这部分内容花费了我好几天，不过收获的真是非常的多！里面有好多的思想和大家分享一下！

（一）流程图
做项目之前一定要理清思路，流程图就是一个非常好的办法，processon就是一个额很好的画图工具，大家可以尝试一下！

（二）、部分代码展示

1、定义函数field，这里的case的作用与if else其实作用是一样的，但是这样写更加方便了！

Public Function field(i As String) As String
 '定义一个函数过程
    Select Case i
            Case "学号"    '如果i要是学号，那么field= "studentno"
        field = "studentno"
            Case "姓名"
        field = "studentName"
            Case "卡号"
        field = "cardno"
            Case "余额"
        field = "cash"
            Case "系别"
        field = "department"
            Case "年级"
        field = "grade"
            Case "班级"
        field = "class"
            Case "性别"
        field = "sex"
            Case "状态"
        field = "status"
            Case "备注"
        field = "explain"
            Case "类型"
        field = "type"
            Case "上机日期"
        field = "ondate"
            Case "上机时间"
        field = "ontime"
            Case "下机日期"
        field = "offdate"
            Case "下机时间"
        field = "offtime"
            Case "或"
        field = "or "
            Case "与"
        field = "and "
   End Select
End Function

2、组合查询部分

Private Sub cmdInqutre_Click()
Dim txtsql As String 
Dim MsgText As String
Dim mrc As ADODB.Recordset '开始进行了组合查询
txtsql = "select * from worklog_info where "
If Trim(cmdField1.Text) = "" Or Trim(cmdOpemark1.Text) =                                                         ""Or Trim(txtContent1.Text) = "" Then
    MsgBox "请将第一行的内容补充完整", vbOKOnly + vbInformation, "提示"
    Exit Sub
Else 
    txtsql = txtsql & field(Trim(cmdField1.Text)) (Trim(cmdOpemark1.Text)) & "'" & (Trim(txtContent1.Text)) & "'"
    If comRelation1.Text <> "" Then
        If Trim(cmdField2.Text) = "" Or Trim(cmdOpemark2.Text) = ""         Or Trim(txtContent2.Text) = "" Then
             MsgBox "已经成功选择了一个组合关系，请将第二行的内容补充完整", vbOKOnly + vbInformation, "提示"
             Exit Sub
        Else
            txtsql = txtsql & field(Trim(comRelation1.Text)) & field(Trim(cmdField2.Text)) & " " & (Trim(cmdOpemark2.Text)) & "'" & (Trim(txtContent2.Text)) & "'"
            If comRelation2.Text <> "" Then
                If Trim(cmdField3.Text) = "" Or Trim(cmdOpemark3.Text) = "" Or Trim(txtContent3.Text) = "" Then
                    MsgBox "已经成功选择了两个组合关系，请将第三行的内容补充完整", vbOKOnly + vbInformation, "提示"
                    Exit Sub
                Else
                    txtsql = txtsql & field(Trim(comRelation2.Text)) & " " & field(Trim(cmdField3.Text)) & " " & (Trim(cmdOpemark3.Text)) & " " & (Trim(txtContent3.Text))
                End If
            End If
        End If
    End If
End If

3、快速清除textbox，和combobox中的内容

 '清除的按钮，快速清除各个空间里的信息
Private Sub cmdCls_Click()
   Dim ctl As Control   '定义一个变量，把控件装载到里面
   For Each ctl In Controls
    '删除所有text文本框内容
   If TypeOf ctl Is TextBox Then ctl.Text = ""               
       Next ctl
   '删除所有combobox文本框内容
   For Each ctl In Controls
       If TypeOf ctl Is ComboBox Then ctl.Text = ""
   Next ctl
  MSFlexGrid1.Clear
End Sub

4、时间控件的小知识（dtpicker）

（1）对于DateTimePicker控件，主要有以下属性：
①.value属性，主要获得DTPicker控件当前选定的日期。
②.format属性：
0-dtpLongDate 显示系统的长日期格式：2017年X月X日
1-dtpShortDate ‘显示短日期格式：如2017/10/10
2-dtpTime ‘显示时间格式：比如，HH:MM:SS
3-dtpCustom ‘可以自定义格式

（2）实现text控件与dtpicker控件的交互，就是说当点击与日期和时间有关的内容时，就让dtpicker控件出现，否则就是text控件！

If cmdField1.Text = "上机日期" Or cmdField1.Text = "下机日期" Then
        DTPicker1.Format = dtpCustom
        DTPicker1.CustomFormat = Format("yyyy-MM-dd")
        DTPicker1.Visible = True
        txtContent1.Visible = False
        txtContent1.Text = DTPicker1.Value
Else
    If cmdField1.Text = "上机时间" Or cmdField1.Text = "下机时间" Then
            DTPicker1.Format = dtpTime
            DTPicker1.Visible = True
            txtContent1.Visible = False
            txtContent1.Text = DTPicker1.Value  
    Else
            DTPicker1.Visible = False
            txtContent1.Visible = True
   End If
End If

5、操作符与查询内容的对应，当我们点击 “姓名”, “卡号”, “备注”内容时，是不可以出现”<”，”>“这两个符号的。

        Select Case cmdField1.Text
        Case "姓名", "卡号", "备注"
            cmdOpemark1.Clear
            cmdOpemark1.AddItem "="
            cmdOpemark1.AddItem "<>"
        Case "上机日期", "下机日期", "上机时间", "下机时间", "余额", "消费金额"
            cmdOpemark1.Clear
            cmdOpemark1.AddItem "="
            cmdOpemark1.AddItem "<>"
            cmdOpemark1.AddItem "<"
            cmdOpemark1.AddItem ">"
        End Select