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点击下载此附件