分类计算余额的函数
时间:2006-10-23 21:50 来源:Access开发者 作者:fan0217@163.com 阅读:次
函数名称: 计算余额
'-功能描述: 分类计算余额
'-输入参数说明: 参数1: 必选 str表 As String 计算余额的表名称
' 参数2: 必选 str日期 As String 日期 排序的字段名称
' 参数3: 必选 str分类 As String 分类的字段名称,字段类型:数字
' 参数4: 必选 str借方 As String 计算余额增加方字段名称,字段类型:数字
' 参数5: 必选 str贷方 As String 计算余额减少方字段名称,字段类型:数字
' 参数6: 必选 str余额 As String 计算的余额字段,字段类型:数字
'-返回参数说明: 计算成功返回True;计算失败返回False
'-使用语法示例: bln = 计算余额("银行存款", "日期", "银行", "存入", "提款", "余额")
'-参考:
'-使用注意: 使用本函数时请保留函数信息内容,需要引用ADO
'-兼容性: 2000,XP,2003
'
'===============================================================================
Function 计算余额(str表 As String, _
str日期 As String, _
str分类 As String, _
str借方 As String, _
str贷方 As String, _
str余额 As String) As Boolean
On Error GoTo Err_计算余额
Dim conn As New ADODB.Connection
Dim rs As New Recordset
Dim rsTemp As New Recordset
Dim strSQL As String
Dim dblBalance As Double
Set conn = CurrentProject.Connection
strSQL = "SELECT DISTINCT " & str分类 & " FROM " & str表
rsTemp.Open strSQL, conn, adOpenKeyset, adLockOptimistic
Do While Not rsTemp.EOF
strSQL = "SELECT * FROM " & str表
strSQL = strSQL & " WHERE " & str分类 & " = " & rsTemp(str分类)
'如果分类字段的数据类型为文本,请使用以下这句代码
'strSQL = strSQL & " WHERE " & str分类 & " = '" & rsTemp(str分类) & "'"
strSQL = strSQL & " ORDER BY " & str日期 & ";"
rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic
dblBalance = 0
Do While Not rs.EOF
rs(str余额) = Nz(rs(str借方), 0) - Nz(rs(str贷方), 0) + dblBalance
dblBalance = rs(str余额)
rs.Update
rs.MoveNext
Loop
rs.Close
rsTemp.MoveNext
Loop
计算余额 = True
rsTemp.Close
Set rsTemp = Nothing
Set rs = Nothing
Set conn = Nothing
Exit_计算余额:
Exit Function
Err_计算余额:
计算余额 = False
Set rsTemp = Nothing
Set rs = Nothing
Set conn = Nothing
MsgBox Err.Description
Resume Exit_计算余额
End Function
'-功能描述: 分类计算余额
'-输入参数说明: 参数1: 必选 str表 As String 计算余额的表名称
' 参数2: 必选 str日期 As String 日期 排序的字段名称
' 参数3: 必选 str分类 As String 分类的字段名称,字段类型:数字
' 参数4: 必选 str借方 As String 计算余额增加方字段名称,字段类型:数字
' 参数5: 必选 str贷方 As String 计算余额减少方字段名称,字段类型:数字
' 参数6: 必选 str余额 As String 计算的余额字段,字段类型:数字
'-返回参数说明: 计算成功返回True;计算失败返回False
'-使用语法示例: bln = 计算余额("银行存款", "日期", "银行", "存入", "提款", "余额")
'-参考:
'-使用注意: 使用本函数时请保留函数信息内容,需要引用ADO
'-兼容性: 2000,XP,2003
'
'===============================================================================
Function 计算余额(str表 As String, _
str日期 As String, _
str分类 As String, _
str借方 As String, _
str贷方 As String, _
str余额 As String) As Boolean
On Error GoTo Err_计算余额
Dim conn As New ADODB.Connection
Dim rs As New Recordset
Dim rsTemp As New Recordset
Dim strSQL As String
Dim dblBalance As Double
Set conn = CurrentProject.Connection
strSQL = "SELECT DISTINCT " & str分类 & " FROM " & str表
rsTemp.Open strSQL, conn, adOpenKeyset, adLockOptimistic
Do While Not rsTemp.EOF
strSQL = "SELECT * FROM " & str表
strSQL = strSQL & " WHERE " & str分类 & " = " & rsTemp(str分类)
'如果分类字段的数据类型为文本,请使用以下这句代码
'strSQL = strSQL & " WHERE " & str分类 & " = '" & rsTemp(str分类) & "'"
strSQL = strSQL & " ORDER BY " & str日期 & ";"
rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic
dblBalance = 0
Do While Not rs.EOF
rs(str余额) = Nz(rs(str借方), 0) - Nz(rs(str贷方), 0) + dblBalance
dblBalance = rs(str余额)
rs.Update
rs.MoveNext
Loop
rs.Close
rsTemp.MoveNext
Loop
计算余额 = True
rsTemp.Close
Set rsTemp = Nothing
Set rs = Nothing
Set conn = Nothing
Exit_计算余额:
Exit Function
Err_计算余额:
计算余额 = False
Set rsTemp = Nothing
Set rs = Nothing
Set conn = Nothing
MsgBox Err.Description
Resume Exit_计算余额
End Function
(责任编辑:admin)
顶一下
(0)
0%
踩一下
(0)
0%
最新内容
推荐内容