专家坐堂-高效流水账，补前几天的记录，如何从这个日期起重新计算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=96147)

--  作者：szsmall
--  发布时间：2017/2/14 11:30:00
--  高效流水账，补前几天的记录，如何从这个日期起重新计算
补前天的记录，应该把昨天和今天的余额重新计算。但现在按[_SortKey]，是从最新的余额计算。
试着把[_SortKey]改[日期]，出现错误

此主题相关图片如下：高效流水账日期错误2.jpg

此主题相关图片如下：高效流水账日期错误.jpg

Select Case e.DataCol.Name
Case "产品","入库","出库"
Dim drs As List(of DataRow)
Dim Filter As String
Filter = "[日期] >= " & e.DataRow("日期") & " And [产品] = \'" & e.DataRow("产品") & "\'"
drs = e.DataTable.Select(Filter)
For Each dr As DataRow In drs
Filter = "[日期] <= " & dr("日期") & " And [产品] = \'" & dr("产品") & "\'"
Dim Val1 As Double = e.DataTable.Compute("Sum(入库)",Filter)
Dim Val2 As Double = e.DataTable.Compute("Sum(出库)",Filter)
dr("库存") = Val1 - Val2
Next
If e.DataCol.Name = "产品" AndAlso e.OldValue IsNot Nothing AndAlso e.OldValue <> e.NewValue Then
Filter = "[日期] > " & e.DataRow("日期") & " And [产品] = \'" & e.OldValue & "\'"
drs = e.DataTable.Select(Filter)
For Each dr As DataRow In drs
Filter = "[日期] <= " & dr("日期") & " And [产品] = \'" & dr("产品") & "\'"
Dim Val1 As Double = e.DataTable.Compute("Sum(入库)",Filter)
Dim Val2 As Double = e.DataTable.Compute("Sum(出库)",Filter)
dr("库存") = Val1 - Val2
Next
End If
End Select

[此贴子已经被作者于2017/2/14 11:34:05编辑过]

--  作者：有点色
--  发布时间：2017/2/14 12:01:00
--

Select Case e.DataCol.Name

Case "产品","入库","出库"

Dim drs As List(of DataRow)

Dim Filter As String

Filter = "[日期] >= #" & e.DataRow("日期") & "# And [产品] = \'" & e.DataRow("产品") & "\'"

drs = e.DataTable.Select(Filter)

For Each dr As DataRow In drs

Filter = "[日期] <= #" & dr("日期") & "# And [产品] = \'" & dr("产品") & "\'"

Dim Val1 As Double = e.DataTable.Compute("Sum(入库)",Filter)

Dim Val2 As Double = e.DataTable.Compute("Sum(出库)",Filter)

dr("库存") = Val1 - Val2

Next

If e.DataCol.Name = "产品" AndAlso e.OldValue IsNot Nothing AndAlso e.OldValue <> e.NewValue Then

Filter = "[日期] > #" & e.DataRow("日期") & "# And [产品] = \'" & e.OldValue & "\'"

drs = e.DataTable.Select(Filter)

For Each dr As DataRow In drs

Filter = "[日期] <= #" & dr("日期") & "# And [产品] = \'" & dr("产品") & "\'"

Dim Val1 As Double = e.DataTable.Compute("Sum(入库)",Filter)

Dim Val2 As Double = e.DataTable.Compute("Sum(出库)",Filter)

dr("库存") = Val1 - Val2

Next

End If

End Select

[此贴子已经被作者于2017/2/14 12:01:13编辑过]

--  作者：szsmall
--  发布时间：2017/2/14 13:02:00
--
代码，计算出来的结果不对。是按时间计算的同时，还要再排序？还是用高效流水账，按[_sortkey]的顺序，找到最新的再计算？

此主题相关图片如下：流水账.jpg

下载信息  [文件大小：   下载次数： ]
点击浏览该文件:高效率的流水账 --按日期.table

[此贴子已经被作者于2017/2/16 8:02:54编辑过]

--  作者：有点色
--  发布时间：2017/2/14 13:16:00
--
Select Case e.DataCol.Name
    Case "产品","入库","出库"
        Dim drs As List(of DataRow)
        Dim Filter As String
        Filter = "[日期] >= #" & e.DataRow("日期") & "# And [产品] = \'" & e.DataRow("产品") & "\'"
        drs = e.DataTable.Select(Filter)
        For Each dr As DataRow In drs
            Filter = "[日期] <= #" & dr("日期") & "# and _sortkey <= " & dr("_sortkey") & " And [产品] = \'" & dr("产品") & "\'"
            Dim Val1 As Double = e.DataTable.Compute("Sum(入库)",Filter)
            Dim Val2 As Double = e.DataTable.Compute("Sum(出库)",Filter)
            dr("库存") = Val1 - Val2
        Next
        If e.DataCol.Name = "产品" AndAlso e.OldValue IsNot Nothing AndAlso e.OldValue <> e.NewValue Then
            Filter = "[日期] > #" & e.DataRow("日期") & "# And [产品] = \'" & e.OldValue & "\'"
            drs = e.DataTable.Select(Filter)
            For Each dr As DataRow In drs
                Filter = "[日期] <= #" & dr("日期") & "# and _sortkey <= " & dr("_sortkey") & " And [产品] = \'" & dr("产品") & "\'"
                Dim Val1 As Double = e.DataTable.Compute("Sum(入库)",Filter)
                Dim Val2 As Double = e.DataTable.Compute("Sum(出库)",Filter)
                dr("库存") = Val1 - Val2
            Next
        End If
End Select

--  作者：szsmall
--  发布时间：2017/2/14 16:04:00
--
新增的一行，余额正确了，但后面日期的并没跟着变

此主题相关图片如下：222.gif

--  作者：有点色
--  发布时间：2017/2/14 17:21:00
--
Select Case e.DataCol.Name
    Case "产品","入库","出库"
        Dim drs As List(of DataRow)
        Dim Filter As String
        Filter = "[日期] >= #" & e.DataRow("日期") & "# And [产品] = \'" & e.DataRow("产品") & "\'"
        drs = e.DataTable.Select(Filter)
        For Each dr As DataRow In drs
            Filter = "iif([日期] < #" & dr("日期") & "#, [日期] < #" & dr("日期") & "# And [产品] = \'" & dr("产品") & "\', [日期] = #" & dr("日期") & "# and _sortkey <= " & dr("_sortkey") & " And [产品] = \'" & dr("产品") & "\')"

            Dim Val1 As Double = e.DataTable.Compute("Sum(入库)",Filter)
            Dim Val2 As Double = e.DataTable.Compute("Sum(出库)",Filter)
            dr("库存") = Val1 - Val2
        Next
        If e.DataCol.Name = "产品" AndAlso e.OldValue IsNot Nothing AndAlso e.OldValue <> e.NewValue Then
            Filter = "[日期] > #" & e.DataRow("日期") & "# And [产品] = \'" & e.OldValue & "\'"
            drs = e.DataTable.Select(Filter)
            For Each dr As DataRow In drs
                Filter = "iif([日期] < #" & dr("日期") & "#, [日期] < #" & dr("日期") & "# And [产品] = \'" & dr("产品") & "\', [日期] = #" & dr("日期") & "# and _sortkey <= " & dr("_sortkey") & " And [产品] = \'" & dr("产品") & "\')"

                Dim Val1 As Double = e.DataTable.Compute("Sum(入库)",Filter)
                Dim Val2 As Double = e.DataTable.Compute("Sum(出库)",Filter)
                dr("库存") = Val1 - Val2
            Next
        End If
End Select

--  作者：szsmall
--  发布时间：2017/2/15 9:34:00
--
排序，日期从旧到新已经可以。但换成新到旧，余额顺序又乱了。我加了序号，发现是排序顺序的问题，有办法做到换排序时，余额也能正确显示。如果实在不行，怎样默认日期从新到旧嘛

此主题相关图片如下：223.gif

此主题相关图片如下：001.jpg

此主题相关图片如下：002.jpg

[此贴子已经被作者于2017/2/15 9:37:51编辑过]

--  作者：有点色
--  发布时间：2017/2/15 10:06:00
--

单独做一个按钮计算。或者写到AfterSort事件去

Dim t As Table = Tables("例子二")
For Each dr As Row In t.Rows
    Dim Val1 As Double = 0
    Dim Val2 As Double = 0
    For i As Integer = 0 To dr.Index
        If t.Rows(i)("产品") = dr("产品") Then
            val1 += t.rows(i)("入库")
            val2 += t.rows(i)("出库")
        End If
    Next
    dr("库存") = Val1 - Val2
Next

--  作者：szsmall
--  发布时间：2017/2/15 10:33:00
--
下面你的代码是什么意思

val1 += t.rows(i)("入库")
val2 += t.rows(i)("出库")

--  作者：有点色
--  发布时间：2017/2/15 10:42:00
--

是累加的意思

等同于 val1 = val1 + t.rows(i)("入库")