[浏览完整版]
标题：明细账余额计算
1楼
实话实说 发表于：2023/12/22 15:35:00
2楼
有点蓝 发表于：2023/12/22 15:54:00参考 ：http://www.foxtable.com/webhelp/topics/2136.htm，看示例二，和示例的产品一样，把总账科目和明细科目都加到查询条件
3楼
实话实说 发表于：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
4楼
实话实说 发表于：2023/12/22 16:42:00把期初的余额都清零了
5楼
有点蓝 发表于：2023/12/22 16:49:00加条件排除掉期初的行呗，比如
dr = e.DataTable.Find("[_SortKey] < " & mr("_SortKey") & " And [总账科目] = '" & mr("总账科目") & "'And [明细科目] = '" & mr("明细科目") & "' and 摘要<>'期初余额'", "[_SortKey] Desc")
6楼
实话实说 发表于：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
7楼
有点蓝 发表于：2023/12/23 9:55:00| 下载信息 [文件大小： 下载次数： ] | |
 点击浏览该文件:明细账.zip |
8楼
实话实说 发表于：2023/12/23 11:30:00能贴出代码吗？我的版本不够。谢谢。
9楼
有点蓝 发表于：2023/12/23 11:45:00Select 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
10楼
实话实说 发表于：2023/12/23 16:52:00对于没有明细科目的行计算出错，代码如何修改？