机房收费系统问题集（4）——指定时间段数据查询+组合查询

来源：互联网发布：sshd 知乎编辑：程序博客网时间：2024/06/13 06:17

最近一直处在“攻坚”阶段，指定时间段数据查询和组合查询两座大山终于拿下，现在整理一下，也不枉我的苦心经营了......

（1）指定时间段数据查询

首先添加DTPicker控件，这是个非常强大的控件，早知道有它，我肯定赚到好多时间......

然后在你需要的地方画出来，它刚刚画出来的样式虽然很普通，但是，运行后，你会大吃一惊的

既能选择你想要的时间，又能立马回到今天，有木有很强大

重点是两个时间段内的数据查询，写入下面的代码，哈哈，尽情的查吧

<pre name="code" class="vb"><strong><span style="font-family:KaiTi_GB2312;font-size:18px;">Private Sub cmdchaxun_Click()Dim txtsql As StringDim msgtext As StringDim mrc As ADODB.RecordsetDim startdate As DateDim enddate As Datestartdate = DTPicker1.Valueenddate = DTPicker2.Valuetxtsql = "select * from Recharge_Info where date >='" & startdate & "' and date <='" & enddate & "'"Set mrc = executesql(txtsql, msgtext)'比较两个日期的大小If DateDiff("d", CDate(startdate), CDate(enddate)) < 0 Then   MsgBox "起始日期不能小于终止日期！", vbOKOnly + vbExclamation, "警告"   Exit SubEnd IfIf mrc.EOF Then      MsgBox "没有数据！", vbOKOnly + vbExclamation, "警告"      Exit SubEnd If With myflexgrid        .CellAlignment = 4        .Rows = 1        .TextMatrix(0, 0) = "卡号"        .TextMatrix(0, 1) = "充值金额"        .TextMatrix(0, 2) = "充值日期"        .TextMatrix(0, 3) = "充值时间"        .TextMatrix(0, 4) = "充值教师"        .TextMatrix(0, 5) = "结账状态"Do While Not mrc.EOF        .CellAlignment = 4        .Rows = .Rows + 1        .TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(2))        .TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(3))        .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(4))        .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(5))        .TextMatrix(.Rows - 1, 4) = Trim(username)        If IsNull(mrc.Fields(7)) = False Then           .TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(7))        Else           .TextMatrix(.Rows - 1, 5) = ""        End If        mrc.MoveNext   LoopEnd Withmrc.Close</span></strong>

在代码中间用到了三个函数：DateDiff函数、IsNull函数和CDate函数

DateDiff函数：表达式：DateDiff(timeinterval,date1,date2 [, firstdayofweek [, firstweekofyear]])

允许数据类型:：timeinterval 表示相隔时间的类型，代码为：

年份 yy、yyyy 季度 qq、q

月份 mm、m

每年的某一日 dy、y

日期 dd、d

星期 wk、ww

工作日 dw

小时 hh

分钟 mi、n

秒 ss、s

毫秒 ms

IsNull函数：IsNull是一个内部函数，判断参数对象是否为空(指出表达式是否不包含任何有效数据)，若是，返回true，否则返回false．

CDate函数：CDate(date)返回表达式，此表达式已被转换为 Date 子类型的 Variant。

CDate 根据系统的区域设置识别日期格式。如果数据的格式不能被日期设置识别，则不能判断年、月、日的正确顺序。另外，如果长日期格式包含表示星期几的字符串，则不能被识别。

（2）组合查询

在机房收费系统中有三个用到组合查询的窗体：学生基本信息维护，学生上机统计信息查询，操作员工作记录，现在以学生基本信息维护为例

首先在模块中将人类语言转换成计算机可以懂的语言，以便于查询

<strong><span style="font-family:KaiTi_GB2312;font-size:18px;">Public Function FiledName(StrFiledName As String) As String    Select Case StrFiledName    Case "卡号"         FiledName = "cardno"    Case "学号"         FiledName = "studentno"    Case "姓名"         FiledName = "studentname"    Case "性别"         FiledName = "sex"    Case "学院"         FiledName = "department"    Case "年级"         FiledName = "grade"    Case "班级"         FiledName = "class"    End Select</span></strong>

<strong><span style="font-family:KaiTi_GB2312;font-size:18px;">End Function</span></strong>

再在模块中添加判断是否为空的代码（学生信息管理系统中也用到了）

<strong><span style="font-family:KaiTi_GB2312;font-size:18px;">Public Function testtxt(txt As String) As Boolean'判断输入内容是否为空If Trim(txt) = "" Then     testtxt = FalseElse     testtxt = TrueEnd IfEnd Function</span></strong>

然后设置操作符，卡号，学号可以有“<”和“>”，可是姓名，性别，系别，年级不能有，于是乎，在每个字段的click事件下添加下面的代码：

<strong><span style="font-family:KaiTi_GB2312;font-size:18px;">Private Sub Comboziduan1_Click()          Combocaozuo1.Clear '添加后就没有重复的操作符了         Select Case Comboziduan1.Text           Case "卡号"                Combocaozuo1.AddItem "="                Combocaozuo1.AddItem "<"                Combocaozuo1.AddItem ">"                Combocaozuo1.AddItem "<>"           Case "学号"                Combocaozuo1.AddItem "="                Combocaozuo1.AddItem "<"                Combocaozuo1.AddItem ">"                Combocaozuo1.AddItem "<>"           Case "姓名"                Combocaozuo1.AddItem "="                Combocaozuo1.AddItem "<>"           Case "性别"                MsgBox "请输入“男”或“女”！", vbOKOnly + vbExclamation, "提示"                Combocaozuo1.AddItem "="                Combocaozuo1.AddItem "<>"           Case "系别"                Combocaozuo1.AddItem "="                Combocaozuo1.AddItem "<>"           Case "年级"                Combocaozuo1.AddItem "="                Combocaozuo1.AddItem "<>"           Case "班级"                Combocaozuo1.AddItem "="                Combocaozuo1.AddItem "<>"    End SelectEnd Sub</span></strong>

再在“查询”的按钮的click事件中添加下面的代码：

<strong><span style="font-family:KaiTi_GB2312;font-size:18px;">Private Sub cmdchaxun_Click()    Dim txtsql As String    Dim msgtext As String    Dim mrc As ADODB.Recordset        txtsql = "select * from student_Info where "      '判断字段是否为空    If Not testtxt(Trim(Comboziduan1.Text)) Then       MsgBox "请选择字段名！", vbOKOnly + vbExclamation, "警告"       Comboziduan1.SetFocus       Exit Sub    End If    '判断操作符是否为空    If Not testtxt(Trim(Combocaozuo1.Text)) Then       MsgBox "请选择操作符！", vbOKOnly + vbExclamation, "警告"       Combocaozuo1.SetFocus       Exit Sub    End If    '判断查询内容是否为空    If Not testtxt(Trim(txtchaxun1.Text)) Then       MsgBox "请输入要查询的内容！", vbOKOnly + vbExclamation, "警告"       txtchaxun1.SetFocus       Exit Sub    End If        txtsql = txtsql & FiledName(Comboziduan1.Text) & " " & Combocaozuo1.Text & "'" & txtchaxun1.Text & "'"     '开始组合    If Trim(Combozuhe1.Text <> "") Then       If Trim(Comboziduan2.Text) = "" Or Trim(Combocaozuo2.Text) = "" Or Trim(txtchaxun2.Text) = "" Then          MsgBox "您选择了第一个组合关系，请输入完整的信息后再查询！", vbOKOnly + vbExclamation, "提示"          Exit Sub       Else          txtsql = txtsql & FiledName(Combozuhe1.Text) & " " & FiledName(Comboziduan2.Text) & Combocaozuo2.Text & "'" & Trim(txtchaxun2.Text) & "'"           End If    End If        If Trim(Combozuhe2.Text) <> "" Then       If Trim(Comboziduan3.Text) = "" Or Trim(Combocaozuo3.Text) = "" Or Trim(txtchaxun3.Text) = "" Then          MsgBox "您选择了第二个组合关系，请输入完整的信息后再查询！", vbOKOnly + vbExclamation, "警告"          Exit Sub       Else         txtsql = txtsql & FiledName(Combozuhe2.Text) & " " & FiledName(Comboziduan3.Text) & Combocaozuo3.Text & "'" & Trim(txtchaxun3.Text) & "'"            End If   End If         '开始查询   Set mrc = executesql(txtsql, msgtext)      If mrc.EOF Then      MsgBox "没有您要查找的学生，请重新查询！", vbOKOnly + vbExclamation, "警告"   Else      With myflexgrid             .Rows = 1             .CellAlignment = 4             .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) = "备注"             .TextMatrix(0, 9) = "状态"             .TextMatrix(0, 10) = "日期"             .TextMatrix(0, 11) = "时间"             .TextMatrix(0, 12) = "类型"       End With                Do While Not mrc.EOF         With myflexgrid              .Rows = .Rows + 1              .CellAlignment = 4              .TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(0))              .TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(1))              .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(2))              .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(3))              .TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(4))              .TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(5))              .TextMatrix(.Rows - 1, 6) = Trim(mrc.Fields(6))              .TextMatrix(.Rows - 1, 7) = Trim(mrc.Fields(7))              .TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(8))              .TextMatrix(.Rows - 1, 9) = Trim(mrc.Fields(10))              .TextMatrix(.Rows - 1, 10) = Trim(mrc.Fields(12))              .TextMatrix(.Rows - 1, 11) = Trim(mrc.Fields(13))              .TextMatrix(.Rows - 1, 12) = Trim(mrc.Fields(14))        End With        mrc.MoveNext    Loop End If </span></strong>

<strong><span style="font-family:KaiTi_GB2312;font-size:18px;">mrc.Close  End Sub</span></strong>

OK，大功告成，查询一下吧，是不是感觉超厉害（其实代码中我还有不懂的地方，还在摸索中，希望大家多多提建议，共同加油！！！）

没有做不到，只有想不到，怀着全心全意为人民服务的赤诚之心，没有做不好的事情！！！小伙伴们，好好加油！！！

0 0