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

前言

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

（一）流程图
做项目之前一定要理清思路，流程图就是一个非常好的办法，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 SelectEnd Function

2、组合查询部分

Private Sub cmdInqutre_Click()Dim txtsql As String Dim MsgText As StringDim 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 SubElse     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 IfEnd 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.ClearEnd 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.ValueElse    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 IfEnd 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