- Foxtable(狐表) (http://foxtable.net/bbs/index.asp)
-- 专家坐堂 (http://foxtable.net/bbs/list.asp?boardid=2)
---- 明细账余额计算 (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=189747)
-- 发布时间：2023/12/22 15:35:00
-- 明细账余额计算
这个明细账表的余额如何根据总账科目和明细科目分类计算？类似流水账，余额 = 余额（上1行） + 借方 - 贷方
| 下载信息 [文件大小： 下载次数： ] | |
 点击浏览该文件:明细账.foxdb |
-- 发布时间：2023/12/22 15:54:00
--
参考 ：http://www.foxtable.com/webhelp/topics/2136.htm，看示例二，和示例的产品一样，把总账科目和明细科目都加到查询条件
-- 发布时间：2023/12/22 16:41:00
--
这样吗？不行啊。
Select Case e.DataCol.Name
Case "总账科目","明细科目","借方","贷方"
Dim dr As DataRow
Dim mr As DataRow = e.DataRow
Dim drs As List(of DataRow)
dr = e.DataTable.Find("[_SortKey] < " & mr("_SortKey") & " And [总账科目] = \'" & mr("总账科目") & "\'And [明细科目] = \'" & mr("明细科目") & "\'", "[_SortKey] Desc")
If dr Is Nothing Then
mr("余额") = mr("借方") - mr("贷方")
dr = mr
End If
drs = e.DataTable.Select("[_SortKey] >= " & dr("_SortKey") & " And [总账科目] = \'" & dr("总账科目") & "\'And [明细科目] = \'" & mr("明细科目") & "\'", "[_SortKey]")
For i As Integer = 1 To drs.Count - 1
drs(i)("余额") = drs(i-1)("余额") + drs(i)("借方") - drs(i)("贷方")
Next
If e.DataCol.Name = "总账科目" AndAlso e.OldValue IsNot Nothing AndAlso e.OldValue <> e.NewValue Then
dr = e.DataTable.Find("[_SortKey] < " & mr("_SortKey") & " And [总账科目] = \'" & e.OldValue & "\'And [明细科目] = \'" & e.OldValue & "\'", "[_SortKey] Desc")
If dr Is Nothing Then
dr = e.DataTable.Find("[总账科目] = \'" & e.OldValue & "\'", "[_SortKey]")
If dr IsNot Nothing Then
dr("余额") = dr("借方") - dr("贷方")
End If
End If
If dr IsNot Nothing Then
drs = e.DataTable.Select("[_SortKey] >= " & dr("_SortKey") & " And [总账科目] = \'" & dr("总账科目") & "\'And [明细科目] = \'" & mr("明细科目") & "\'", "[_SortKey]")
For i As Integer = 1 To drs.Count - 1
drs(i)("余额") = drs(i-1)("余额") + drs(i)("借方") - drs(i)("贷方")
Next
End If
End If
End Select
-- 发布时间：2023/12/22 16:42:00
--
把期初的余额都清零了
-- 发布时间：2023/12/22 16:49:00
--
加条件排除掉期初的行呗，比如
dr = e.DataTable.Find("[_SortKey] < " & mr("_SortKey") & " And [总账科目] = \'" & mr("总账科目") & "\'And [明细科目] = \'" & mr("明细科目") & "\' and 摘要<>\'期初余额\'", "[_SortKey] Desc")
-- 发布时间：2023/12/22 18:03:00
--
还是不行
Select Case e.DataCol.Name
Case "总账科目","明细科目","借方","贷方"
Dim dr As DataRow
Dim mr As DataRow = e.DataRow
Dim drs As List(of DataRow)
\'dr = e.DataTable.Find("[_SortKey] < " & mr("_SortKey") & " And [总账科目] = \'" & mr("总账科目") & "\'And [明细科目] = \'" & mr("明细科目") & "\'", "[_SortKey] Desc")
dr = e.DataTable.Find("[_SortKey] < " & mr("_SortKey") & " And [总账科目] = \'" & mr("总账科目") & "\'And [明细科目] = \'" & mr("明细科目") & "\' and 摘要<>\'期初余额\'", "[_SortKey] Desc")
If dr Is Nothing Then
mr("余额") = mr("借方") - mr("贷方")
dr = mr
End If
drs = e.DataTable.Select("[_SortKey] >= " & dr("_SortKey") & " And [总账科目] = \'" & dr("总账科目") & "\'And [明细科目] = \'" & mr("明细科目") & "\'and 摘要<>\'期初余额\'", "[_SortKey]")
For i As Integer = 1 To drs.Count - 1
drs(i)("余额") = drs(i-1)("余额") + drs(i)("借方") - drs(i)("贷方")
Next
If e.DataCol.Name = "总账科目" AndAlso e.OldValue IsNot Nothing AndAlso e.OldValue <> e.NewValue Then
dr = e.DataTable.Find("[_SortKey] < " & mr("_SortKey") & " And [总账科目] = \'" & e.OldValue & "\'And [明细科目] = \'" & e.OldValue & "\'and 摘要<>\'期初余额\'", "[_SortKey] Desc")
If dr Is Nothing Then
dr = e.DataTable.Find("[总账科目] = \'" & e.OldValue & "\'And [明细科目] = \'" & e.OldValue & "\'and 摘要<>\'期初余额\'", "[_SortKey]")
If dr IsNot Nothing Then
dr("余额") = dr("借方") - dr("贷方")
End If
End If
If dr IsNot Nothing Then
drs = e.DataTable.Select("[_SortKey] >= " & dr("_SortKey") & " And [总账科目] = \'" & dr("总账科目") & "\'And [明细科目] = \'" & mr("明细科目") & "\', And 摘要<>\'期初余额\'", "[_SortKey]")
For i As Integer = 1 To drs.Count - 1
drs(i)("余额") = drs(i-1)("余额") + drs(i)("借方") - drs(i)("贷方")
Next
End If
End If
End Select
-- 发布时间：2023/12/23 9:55:00
--
| 下载信息 [文件大小： 下载次数： ] | |
 点击浏览该文件:明细账.zip |
-- 发布时间：2023/12/23 11:30:00
--
能贴出代码吗？我的版本不够。谢谢。
-- 发布时间：2023/12/23 11:45:00
--
Select Case e.DataCol.Name
Case "总账科目", "明细科目", "借方", "贷方"
Dim dr As DataRow
Dim mr As DataRow = e.DataRow
If mr("摘要") = "期初余额" Then
Return
End If
Dim drs As List(Of DataRow)
\'dr = e.DataTable.Find("[_SortKey] < " & mr("_SortKey") & " And [总账科目] = \'" & mr("总账科目") & "\'And [明细科目] = \'" & mr("明细科目") & "\'", "[_SortKey] Desc")
dr = e.DataTable.Find("[_SortKey] < " & mr("_SortKey") & " And [总账科目] = \'" & mr("总账科目") & "\'And [明细科目] = \'" & mr("明细科目") & "\'", "[_SortKey] Desc")
If dr Is Nothing Then
mr("余额") = mr("借方") - mr("贷方")
dr = mr
End If
drs = e.DataTable.Select("[_SortKey] >= " & dr("_SortKey") & " And [总账科目] = \'" & dr("总账科目") & "\'And [明细科目] = \'" & mr("明细科目") & "\'", "[_SortKey]")
For i As Integer = 1 To drs.Count - 1
drs(i)("余额") = drs(i - 1)("余额") + drs(i)("借方") - drs(i)("贷方")
Next
If e.DataCol.Name = "总账科目" AndAlso e.OldValue IsNot Nothing AndAlso e.OldValue <> e.NewValue Then
dr = e.DataTable.Find("[_SortKey] < " & mr("_SortKey") & " And [总账科目] = \'" & e.OldValue & "\'And [明细科目] = \'" & mr("明细科目") & "\'", "[_SortKey] Desc")
If dr Is Nothing Then
dr = e.DataTable.Find("[总账科目] = \'" & e.OldValue & "\'And [明细科目] = \'" & mr("明细科目") & "\'", "[_SortKey]")
If dr IsNot Nothing Then
dr("余额") = dr("借方") - dr("贷方")
End If
End If
If dr IsNot Nothing Then
drs = e.DataTable.Select("[_SortKey] >= " & dr("_SortKey") & " And [总账科目] = \'" & dr("总账科目") & "\' And [明细科目] = \'" & mr("明细科目") & "\'", "[_SortKey]")
For i As Integer = 1 To drs.Count - 1
drs(i)("余额") = drs(i - 1)("余额") + drs(i)("借方") - drs(i)("贷方")
Next
End If
End If
If e.DataCol.Name = "明细科目" AndAlso e.OldValue IsNot Nothing AndAlso e.OldValue <> e.NewValue Then
dr = e.DataTable.Find("[_SortKey] < " & mr("_SortKey") & " And [总账科目] = \'" & mr("总账科目") & "\'And [明细科目] = \'" & e.OldValue & "\'", "[_SortKey] Desc")
If dr Is Nothing Then
dr = e.DataTable.Find("[总账科目] = \'" & mr("总账科目") & "\'And [明细科目] = \'" & e.OldValue & "\'", "[_SortKey]")
If dr IsNot Nothing Then
dr("余额") = dr("借方") - dr("贷方")
End If
End If
If dr IsNot Nothing Then
drs = e.DataTable.Select("[_SortKey] >= " & dr("_SortKey") & " And [总账科目] = \'" & mr("总账科目") & "\' And [明细科目] = \'" & dr("明细科目") & "\'", "[_SortKey]")
For i As Integer = 1 To drs.Count - 1
drs(i)("余额") = drs(i - 1)("余额") + drs(i)("借方") - drs(i)("贷方")
Next
End If
End If
End Select
-- 发布时间：2023/12/23 16:52:00
--
对于没有明细科目的行计算出错，代码如何修改？