Office中國論壇/Access中國論壇 › Access中國門戶舊版 ›Sql ServerOracle其它 › 查看內(nèi)容

分類計算余額的函數(shù)

1970-1-1 08:00| 發(fā)布者: fan0217@163.com| 查看: 3440| 評論: 0

函數(shù)名稱:    計算余額
'-功能描述:    分類計算余額
'-輸入?yún)?shù)說明: 參數(shù)1: 必選 str表 As String 計算余額的表名稱
'             參數(shù)2: 必選 str日期 As String 日期排序的字段名稱
'             參數(shù)3: 必選 str分類 As String 分類的字段名稱，字段類型：數(shù)字
'             參數(shù)4: 必選 str借方 As String 計算余額增加方字段名稱，字段類型：數(shù)字
'             參數(shù)5: 必選 str貸方 As String 計算余額減少方字段名稱，字段類型：數(shù)字
'             參數(shù)6: 必選 str余額 As String 計算的余額字段，字段類型：數(shù)字
'-返回參數(shù)說明: 計算成功返回True；計算失敗返回False
'-使用語法示例: bln = 計算余額("銀行存款", "日期", "銀行", "存入", "提款", "余額")
'-參考:
'-使用注意:    使用本函數(shù)時請保留函數(shù)信息內(nèi)容，需要引用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分類)
      '如果分類字段的數(shù)據(jù)類型為文本，請使用以下這句代碼
      '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

收藏分享邀請

帳號		自動登錄	找回密碼
密碼			注冊

分類計算余額的函數(shù)

最新評論

相關分類