第一次机房收费系统之日周结帐单（二）

本文主要是介绍第一次机房收费系统之日周结帐单（二），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

前言：
上一篇讲了如何制作结帐单报表模板，今天来说一下VB机房的账单查询，日结账单是查询一天的账单，周结账单是查询一天或几天的账单，所以周结账单也包括了日结账单的功能。
在窗体上添加GRDisplayViewer控件，接收报表。

在这里插入图片描述
重要代码：
在“刷新”按钮里计算充值卡余额，充值，消费，退卡金额并跟新到结帐单的数据表里。

Dim txtsql As StringDim msgtext As StringDim mrc As ADODB.RecordsetDim mrcc As ADODB.RecordsetDim mrccc As ADODB.RecordsetDim mrcccc As ADODB.RecordsetDim mrccccc As ADODB.RecordsetDim mrcccccc As ADODB.RecordsetDim CZK As CurrencyDim SCZ As CurrencyDim XF As CurrencyDim TK As CurrencyDim cz As CurrencyIf DTPicker1.Value > DTPicker2.Value ThenMsgBox "终止时间不能小于起始时间！", vbOKOnly + vbExclamation, "警告"Exit SubEnd If'计算本期消费金额txtsql = "select sum(consumecash) from Checkday_Info where date between'" & Format$(DTPicker1.Value, "yyyy-mm-dd") & "'" & "and'" & Format$(DTPicker2.Value, "yyyy-mm-dd") & "'"Set mrc = ExecuteSQL(txtsql, msgtext)Debug.Print txtsqlIf IsNull(mrc.Fields(0)) = True ThenXF = 0ElseXF = Trim(mrc.Fields(0))End If'计算本期退卡金额txtsql = "select sum(cancelcash) from Checkday_Info where date between'" & Format$(DTPicker1.Value, "yyyy-mm-dd") & "'" & "and'" & Format$(DTPicker2.Value, "yyyy-mm-dd") & "'"Set mrcc = ExecuteSQL(txtsql, msgtext)Debug.Print txtsqlIf IsNull(mrcc.Fields(0)) = True ThenTK = 0ElseTK = Trim(mrcc.Fields(0))End If'计算本期充值卡余额txtsql = "select sum(allcash) from CheckDay_Info where date between '" & Format(CDate(DTPicker1.Value)) & "' and '" & Format(CDate(DTPicker2.Value)) & "'"Set mrccc = ExecuteSQL(txtsql, msgtext)Debug.Print txtsqlIf IsNull(mrccc.Fields(0)) = True ThenCZK = 0ElseCZK = Trim(mrccc.Fields(0))End If'计算本期充值金额txtsql = "select sum(rechargecash) from CheckDay_Info where date between '" & Format(CDate(DTPicker1.Value)) & "' and '" & Format(CDate(DTPicker2.Value)) & "'"Set mrcccc = ExecuteSQL(txtsql, msgtext)Debug.Print txtsqlIf IsNull(mrcccc.Fields(0)) = True Thencz = 0Elsecz = Trim(mrcccc.Fields(0))End If'计算上期充值卡金额txtsql = "select sum(allcash) from CheckDay_Info where date < '" & CDate(DTPicker1.Value) & "'"Set mrccccc = ExecuteSQL(txtsql, msgtext)Debug.Print txtsqlIf IsNull(mrccccc.Fields(0)) = True ThenSCZ = 0ElseSCZ = Trim(mrccccc.Fields(0))End Iftxtsql = "select * from checkWeek_Info "Set mrcccccc = ExecuteSQL(txtsql, msgtext)mrcccccc.AddNewmrcccccc.Fields(0) = Trim(SCZ)mrcccccc.Fields(1) = Trim(cz)mrcccccc.Fields(2) = Trim(XF)mrcccccc.Fields(3) = Trim(TK)mrcccccc.Fields(4) = Trim(CZK)mrcccccc.Fields(5) = Trim(Date)mrcccccc.Update
Report.DetailGrid.Recordset.QuerySQL = "select  * from checkWeek_Info where date between '" & DTPicker1.Value & "' and '" & DTPicker2.Value & "' "    '通过SELECT查询创建记录集Report.ParameterByName("begindate").Value = Format$(DTPicker1.Value, "yyyy-mm-dd")Report.ParameterByName("enddate").Value = Format$(DTPicker2.Value, "yyyy-mm-dd")GRDisplayViewer1.Refresh '刷新

定义报表对象

'定义报表对象Dim Report As grproLibCtl.GridppReport

在窗体加载事件交互报表模板

Set Report = New grproLibCtl.GridppReport                             '实例化模版Report.LoadFromFile (App.Path & "\机房收入周汇总表.grf")               '加载模版Report.DetailGrid.Recordset.ConnectionString = ConnectString() '连接数据源Report.DetailGrid.Recordset.QuerySQL = "select  * from checkWeek_Info " '通过SELECT查询创建记录集Report.ParameterByName("begindate").Value = Format$(DTPicker1.Value, "yyyy-mm-dd")Report.ParameterByName("enddate").Value = Format$(DTPicker2.Value, "yyyy-mm-dd")Report.ParameterByName("XX").Value = UserNameGRDisplayViewer1.Report = ReportGRDisplayViewer1.Start                                         '开始打印