专家坐堂-[求助]当流水账借方金额去贷方金额余额为负数时，在“借贷方向”列自动填入“”贷“”Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.com/bbs/index.asp)
--  专家坐堂  (http://foxtable.com/bbs/list.asp?boardid=2)
----  [求助]当流水账借方金额去贷方金额余额为负数时，在“借贷方向”列自动填入“”贷“”  (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=132009)

--  作者：time
--  发布时间：2019/3/11 19:33:00
--  [求助]当流水账借方金额去贷方金额余额为负数时，在“借贷方向”列自动填入“”贷“”
  请教：当流水账借方金额减去贷方金额余额为负数时，在“借贷方向”列自动填入“贷”；当流水账借方金额减去贷方金额余额为正数时，在“借贷方向”列自动填入“借”；当流水账借方金额减去贷方金额余额为“0”时，在“借贷方向”列自动填入“平”；

--  作者：有点蓝
--  发布时间：2019/3/11 20:10:00
--
例如

Select Case e.DataCol.Name
Case "收入","支出"
For Each dr As DataRow In e.DataTable.Select("[_SortKey] >= " & e.DataRow("_SortKey"))
Dim Val1 As Double = e.DataTable.Compute("Sum(收入)","[_SortKey] <= " & dr("_SortKey"))
Dim Val2 As Double = e.DataTable.Compute("Sum(支出)","[_SortKey] <= " & dr("_SortKey"))
dr("余额") = Val1 - Val2
Next
Case "余额"
If e.newvalue = 0 Then
e.DataRow("方向") = "平"
ElseIf e.newvalue > 0 Then
e.DataRow("方向") = "借"
Else
e.DataRow("方向") = "贷"
End If
End Select

--  作者：time
--  发布时间：2019/3/11 21:23:00
--
请问：以下代码错在哪里？

Select Case e.DataCol.Name
Case "借方发生额","贷方发生额"
For Each dr As DataRow In e.DataTable.Select("[_SortKey] >= " & e.DataRow("_SortKey"))
Dim Val1 As Double = e.DataTable.Compute("Sum(借方发生额)","[_SortKey] <= " & dr("_SortKey"))
Dim Val2 As Double = e.DataTable.Compute("Sum(贷方发生额)","[_SortKey] <= " & dr("_SortKey"))
dr("余额") = Val1 - Val2
Next
End Select

\'明细余额列数据DataColChanged事件代码改为:

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("明细科目") & "\'", "[_SortKey] Desc")
If dr Is Nothing Then
mr("明细余额") = mr("借方发生额") - mr("贷方发生额")
dr = mr
End If
drs = e.DataTable.Select("[_SortKey] >= " & dr("_SortKey") & " And [明细科目] = \'" & dr("明细科目") & "\'", "[_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 & "\'", "[_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("明细科目") & "\'", "[_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

\'科目余额列数据DataColChanged事件代码改为:

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("会计科目") & "\'", "[_SortKey] Desc")
If dr Is Nothing Then
mr("科目余额") = mr("借方发生额") - mr("贷方发生额")
dr = mr
End If
drs = e.DataTable.Select("[_SortKey] >= " & dr("_SortKey") & " And [会计科目] = \'" & dr("会计科目") & "\'", "[_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 & "\'", "[_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("会计科目") & "\'", "[_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

\'例如

Select Case e.DataCol.Name
Case "借方发生额","贷方发生额"
For Each dr As DataRow In e.DataTable.Select("[_SortKey] >= " & e.DataRow("_SortKey"))
Dim Val1 As Double = e.DataTable.Compute("Sum(借方发生额)","[_SortKey] <= " & dr("_SortKey"))
Dim Val2 As Double = e.DataTable.Compute("Sum(贷方发生额)","[_SortKey] <= " & dr("_SortKey"))
dr("明细余额") = Val1 - Val2
Next
Case "明细余额"
If e.newvalue = 0 Then
e.DataRow("余额方向") = "平"
ElseIf e.newvalue > 0 Then
e.DataRow("余额方向") = "借"
Else
e.DataRow("余额方向") = "贷"
End If
End Select

--  作者：有点蓝
--  发布时间：2019/3/11 21:43:00
--
截图说明，什么地方有问题？出什么错？

--  作者：time
--  发布时间：2019/3/11 22:07:00
--
我把您提供的代码，放在原来的代码后面，能实现自动显示借贷方向，但是明细余额数据变得不按照原来的“明细余额”运算，也就是某一客户的余额不正确。

请教：能不能这样判断：当指定的列比如“明细余额”数据大于0时，借贷方向列自动显示借，当小于0时，“明细余额”自动显示贷，当“明细余额”等于0时，自动显示平。

请教代码怎么写？谢谢！！！

--  作者：有点蓝
--  发布时间：2019/3/11 22:15:00
--
代码没有问题，上传具体实例说明

--  作者：time
--  发布时间：2019/3/11 22:18:00
--
衷心感谢您的指导！

--  作者：time
--  发布时间：2019/3/12 20:12:00
--

好了，可以实现了，只是调整了一下顺序，就正常了，谢谢老大的提示！

Select Case e.DataCol.Name
Case "借方发生额","贷方发生额"
For Each dr As DataRow In e.DataTable.Select("[_SortKey] >= " & e.DataRow("_SortKey"))
Dim Val1 As Double = e.DataTable.Compute("Sum(借方发生额)","[_SortKey] <= " & dr("_SortKey"))
Dim Val2 As Double = e.DataTable.Compute("Sum(贷方发生额)","[_SortKey] <= " & dr("_SortKey"))
dr("余额") = Val1 - Val2
Next
End Select

Select Case e.DataCol.Name
Case "借方发生额","贷方发生额"
For Each dr As DataRow In e.DataTable.Select("[_SortKey] >= " & e.DataRow("_SortKey"))
Dim Val1 As Double = e.DataTable.Compute("Sum(借方发生额)","[_SortKey] <= " & dr("_SortKey"))
Dim Val2 As Double = e.DataTable.Compute("Sum(贷方发生额)","[_SortKey] <= " & dr("_SortKey"))
dr("明细余额") = Val1 - Val2
Next
Case "明细余额"
If e.newvalue = 0 Then
e.DataRow("余额方向") = "平"
ElseIf e.newvalue > 0 Then
e.DataRow("余额方向") = "借"
Else
e.DataRow("余额方向") = "贷"
End If
End Select

\'明细余额列数据DataColChanged事件代码改为:

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("明细科目") & "\'", "[_SortKey] Desc")
If dr Is Nothing Then
mr("明细余额") = mr("借方发生额") - mr("贷方发生额")
dr = mr
End If
drs = e.DataTable.Select("[_SortKey] >= " & dr("_SortKey") & " And [明细科目] = \'" & dr("明细科目") & "\'", "[_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 & "\'", "[_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("明细科目") & "\'", "[_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

\'科目余额列数据DataColChanged事件代码改为:

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("会计科目") & "\'", "[_SortKey] Desc")
If dr Is Nothing Then
mr("科目余额") = mr("借方发生额") - mr("贷方发生额")
dr = mr
End If
drs = e.DataTable.Select("[_SortKey] >= " & dr("_SortKey") & " And [会计科目] = \'" & dr("会计科目") & "\'", "[_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 & "\'", "[_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("会计科目") & "\'", "[_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

\'例如
\'Select Case e.DataCol.Name
\' Case "借方发生额","贷方发生额"
\' For Each dr As DataRow In e.DataTable.Select("[_SortKey] >= " & e.DataRow("_SortKey"))
\' Dim Val1 As Double = e.DataTable.Compute("Sum(借方发生额)","[_SortKey] <= " & dr("_SortKey"))
\' Dim Val2 As Double = e.DataTable.Compute("Sum(贷方发生额)","[_SortKey] <= " & dr("_SortKey"))
\' dr("明细余额") = Val1 - Val2
\' Next
\' Case "明细余额"

\' If e.newvalue = 0 Then
\' e.DataRow("余额方向") = "平"
\' ElseIf e.newvalue > 0 Then
\' e.DataRow("余额方向") = "借"
\' Else
\' e.DataRow("余额方向") = "贷"
\' End If
\'End Select