DAO计算累计
时 间:2019-04-15 15:48:23
作 者:半夜罗 ID:36948 城市:成都
摘 要:累计
正 文:
在实际中,经常用到逐行累计,有单一字段的累计,有多字段累计,在查询中计算不占资源,但数据量大时,速度极慢,并且会出现文本框获得焦点后其值也会改变的情况,在表中计算又会遇到一个系统中有多个类似的表,每个表的计算基本类似,总想用函数来完成,经过多次失败,多次在本站请教,终于成功写出了这个函数,现分享给初学Access的
Function 分组累计余额(str表名称 As String, _
str序号 As String, _
str分组 As String, _
str借方 As String, _
str贷方 As String, _
str余额 As String)
'调用:call 分组累计余额("表名称","序号字段","分组字段","借方","贷方","余额")
'分组:分组字段,文本型
'序号:数字型
'借方、贷方:文本与数字都可
Dim rst As DAO.Recordset
Dim strSql As String
Dim f As String
Dim y As Double
strSql = "Select * FROM " & str表名称
strSql = strSql & " orDER BY " & str分组 & "," & str序号 & ";"
Set rst = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
Do While Not rst.EOF
rst.Edit
If f <> rst(str分组) Then
f = rst(str分组)
y = 0
End If
rst(str余额) = Nz(rst(str借方), 0) - Nz(rst(str贷方), 0) + y
y = rst(str余额)
rst.Update
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Function
Function 不分组累计余额(str表名称 As String, _
str序号 As String, _
str借方 As String, _
str贷方 As String, _
str余额 As String)
'调用:call 不分组累计余额("测试表","序号","借方","贷方","余额")
'序号:数字型
'借方、贷方:文本与数字都可
Dim rst As DAO.Recordset
Dim strSql As String
Dim f As String
Dim y As Double
strSql = "Select * FROM " & str表名称
strSql = strSql & " orDER BY " & str序号 & ";"
Set rst = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
Do While Not rst.EOF
rst.Edit
rst(str余额) = Nz(rst(str借方), 0) - Nz(rst(str贷方), 0) + y
y = rst(str余额)
rst.Update
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Function
Function 分组累计字段(str表名称 As String, _
str序号 As String, _
str分组 As String, _
str金额 As String)
'调用:call 分组累计字段("表名称","序号字段","分组字段","金额字段")
'分组:分组字段,文本型
'序号:数字型
Dim rst As DAO.Recordset
Dim strSql As String
Dim f As String
Dim y As Double
strSql = "Select * FROM " & str表名称
strSql = strSql & " orDER BY " & str分组 & "," & str序号 & ";"
Set rst = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
Do While Not rst.EOF
rst.Edit
If f <> rst(str分组) Then
f = rst(str分组)
y = 0
End If
rst(str金额) = Nz(rst(str金额)) + y
y = rst(str金额)
rst.Update
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Function
Function 不分组累计字段(str表名称 As String, _
str序号 As String, _
str金额 As String)
'调用:call 不分组累计字段("表名称","序号字段","金额")
'序号:数字型
Dim rst As DAO.Recordset
Dim strSql As String
Dim y As Double
strSql = "Select * FROM " & str表名称
strSql = strSql & " orDER BY " & str序号 & ";"
Set rst = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
y = 0
Do While Not rst.EOF
rst.Edit
rst(str金额) = rst(str金额) + y
y = rst(str金额)
rst.Update
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Function点击下载此附件
Access软件网QQ交流群 (群号:54525238) Access源码网店
常见问答:
技术分类:
源码示例
- 【源码QQ群号19834647...(12.17)
- 【Access选项卡示例】Ac...(09.09)
- 【Access源码示例】按输入...(09.02)
- 【Access日期区间段查询】...(08.29)
- 【Access日期区间段查询】...(08.27)
- Access怎样才能实现日期时...(08.21)
- 【Access定时打开查询】A...(08.19)
- Access生成固定数量的记录...(08.13)
- Access怎样才能实现日期时...(08.12)
- Access利用导航窗体控件对...(08.03)
学习心得
最新文章
- Access表中的字段名、字段标题...(09.19)
- Access快速开发平台--更改“...(09.18)
- 【中秋及国庆优惠】Access培训...(09.15)
- Access如何将日期型的数值转换...(09.14)
- 英文输入法输入数据中存在单引号引起...(09.11)
- 【Access选项卡示例】Acce...(09.09)
- 让Access光标停留在指定的控件...(09.07)
- 关于Access查询条件里使用通配...(09.06)
- Access报表偷懒制作法--Ac...(09.05)
- Access快速开发平台--窗体数...(09.04)