IT资讯
分类计算余额的函数
2006-10-23 21:50:59
函数名称: 计算余额'-功能描述: 分类计算余额'-输入参数说明: 参数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 BooleanOn Error GoTo Err_计算余额Dim conn As New ADODB.ConnectionDim rs As New RecordsetDim rsTemp As New RecordsetDim strSQL As StringDim 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