统计转移 - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

最大最小日期

Dim dlst As new List(of Date)

Dim tns() As String = { "订单附表","回款记录" } '其它表名自己加

Dim cls() As String = { "业务往来_订单日期","回款日期" } '其它日期名自己加

For i As Integer = 0 To tns.Length - 1

dlst.Add(DataTables(tns(i)).Compute("min(" & cls(i) & ")"))

dlst.Add(DataTables(tns(i)).Compute("max(" & cls(i) & ")"))

Dim arr = dlst.ToArray

Array.Sort(arr)

Dim minDate As Date = arr(0)

Dim maxDate As Date = arr(arr.Length -1)

第三行红色字不用改

除了最大/最小日期取值之外，其它照样定义，不用变

Dim dt_cwhz As DataTable = DataTables("财务汇总")

Dim dt_ddfb As DataTable = DataTables("订单附表")

Dim dt_hkjl As DataTable = DataTables("回款记录")

Dim dt_tzmx As DataTable = DataTables("投资明细")

Dim dt_xjmx As DataTable = DataTables("现金明细")

Dim dt_qkmx As DataTable = DataTables("欠款明细")

Dim dt_zczm As DataTable = DataTables("支出账目")

Dim dt_zcmx As DataTable = DataTables("支出明细")

For i As Integer = minDate.Year To maxDate.Year

For j As Integer = 1 To 12

Dim ndr As DataRow = dt_cwhz.AddNew

Dim d As Date = new Date(i, j, 1)

Dim filter As String = "业务往来_订单日期 >= #" & d & "# and 业务往来_订单日期 < #" & d.AddMonths(1) & "#"

ndr("年份") = i

ndr("月份") = j

ndr("经营_增加货款") = val(dt_ddfb.compute("sum(订单核算_采购合计)", filter))

ndr("经营_增加利润") = val(dt_ddfb.compute("sum(订单核算_单项利润)", filter))

ndr("经营_税费") = val(dt_ddfb.compute("sum(订单核算_税费)", filter))

ndr("经营_返点") = val(dt_ddfb.compute("sum(订单核算_合计返点)", filter))

ndr("经营_运费") = val(dt_ddfb.compute("sum(订单核算_运费)", filter))

Dim fdr As DataRow = dt_ddfb.Find(filter, "业务往来_订单日期 desc")

filter = "回款日期 >= #" & d & "# and 回款日期 < #" & d.AddMonths(1) & "#"

ndr("经营_回收货款") = val(dt_hkjl.compute("sum(收款金额)", filter))

Dim fdr As DataRow = dt_hkjl.Find(filter, "回款日期 desc")

filter = "变动日期 >= #" & d & "# and 变动日期 < #" & d.AddMonths(1) & "#"

ndr("投资_增加金额") = val(dt_tzmx.compute("sum(再投资金)", filter))

ndr("投资_减少金额") = val(dt_tzmx.compute("sum(撤出资金)", filter))

fdr = dt_tzmx.Find(filter, "变动日期 desc")

If fdr IsNot Nothing Then

ndr("投资_在运资金") = fdr("可控资金")

Else

ndr("投资_在运资金") = 0

End If

ndr("可控_流入") = val(dt_xjmx.compute("sum(流入金额)", filter))

ndr("可控_流出") = val(dt_xjmx.compute("sum(流出金额)", filter))

fdr = dt_xjmx.Find(filter, "变动日期 desc")

If fdr IsNot Nothing Then

ndr("可控_可控资金") = fdr("可控金额")

Else

ndr("可控金额") = 0

End If

。。。。。。

自己核对一下，第一个操作的是什么表，表里的日期列名称是什么

第二个操作的是什么表，第二个表里的日期列名称又是什么

一一对应

看17楼，操作什么表就用这个表对应的列名称，不同的列名称使用的filter就不一样。

你可以在循环里只操作一个表，调通了，在加上第二个表，以此类推

看12楼。

如果是根据统计表已有的月份进行填充，看26楼

Dim d As Date = Date.Today.AddMonths(-1)

For Each ndr As DataRow In dt_tj.Select("","年份 >= " & d.Year & " and 月份 >=" & d.Month)

.......

iif的嵌套用法

Dim a=1,b=2,c=3

Output.Show("最小值=" & iif(a<b, iif(a<c,a,c), iif(b<c,b,c)))

主题：统计转移