主题：统计问题

希望在一个窗口的两个日期输入框中输入日期（默认为本月）范围后，点击一个按钮，能够统计"开piao明细"表中的"产品"，“型号”，不重复的“规格”，的“数量”和“金额”。到“产成品收付”表中的“本月付出_数量”和“本月付出_金额”里。而且是合并模式下“产品”“型号”为升序。日期为降序。并且自动填入上月的到“上月付出_数量”“上月付出_金额”

请上传具体例子说明

下载信息  [文件大小：   下载次数： ]

点击浏览该文件:开piao管理.table

我尝试了先刷选开piao明细表，然后在从明细表生成统计表，但是统计出来的总是所有。刷选没起到作用。还尝试了统计副本表，也失败了.....

参考代码

 

Dim dt As DataTable = DataTables("产成品收付存")
dt.DataRows.Clear
Dim dt1 As DataTable = DataTables("开piao明细")
Dim d1 As Date = new Date(Date.Today.year, Date.Today.Month,1)
Dim d2 As Date = d1.AddMonths(1)
For Each ary() As String In dt1.GetValues("型号|规格|单位")
    Dim ndr As DataRow = dt.AddNew
    ndr("型号") = ary(0)
    ndr("规格") = ary(1)
    ndr("单位") = ary(2)
    Dim filter As String = "型号 = '" & ary(0) & "' and 规格 = '" & ary(1) & "' and 单位 = '" & ary(2) & "'"
    filter = filter.Replace("*", "[*]")
    output.show(filter & " and 开piao日期 < #" & d1 & "#")
    Dim psum_sl As Double = dt1.Compute("sum(数量)", filter & " and 开piao日期 < #" & d1 & "#")
    Dim sum_sl_in As Double = dt1.Compute("sum(数量)", filter & " and 开piao日期 >= #" & d1 & "# and 开piao日期 < #" & d2 & "# and 数量 >= 0")
    Dim sum_sl_out As Double = dt1.Compute("sum(数量)", filter & " and 开piao日期 >= #" & d1 & "# and 开piao日期 < #" & d2 & "# and 数量 <= 0")
    ndr("上月结存_数量") = psum_sl
    ndr("本月收进_数量") = sum_sl_in
    ndr("本月付出_数量") = sum_sl_out
Next

 

此主题相关图片如下：qq截图20170919133805.png

代码我测试没问题，如果选日期，修改红色代码即可

 

Dim dt As DataTable = DataTables("产成品收付存")
dt.DataRows.Clear
Dim dt1 As DataTable = DataTables("开piao明细")
Dim d1 As Date = new Date(Date.Today.year, Date.Today.Month,1)
Dim d2 As Date = d1.AddMonths(1)
For Each ary() As String In dt1.GetValues("型号|规格|单位")
    Dim ndr As DataRow = dt.AddNew
    ndr("型号") = ary(0)
    ndr("规格") = ary(1)
    ndr("单位") = ary(2)
    Dim filter As String = "型号 = '" & ary(0) & "' and 规格 = '" & ary(1) & "' and 单位 = '" & ary(2) & "'"
    filter = filter.Replace("*", "[*]")
    output.show(filter & " and 开piao日期 < #" & d1 & "#")
    Dim psum_sl As Double = dt1.Compute("sum(数量)", filter & " and 开piao日期 < #" & d1 & "#")
    Dim sum_sl_in As Double = dt1.Compute("sum(数量)", filter & " and 开piao日期 >= #" & d1 & "# and 开piao日期 < #" & d2 & "# and 数量 >= 0")
    Dim sum_sl_out As Double = dt1.Compute("sum(数量)", filter & " and 开piao日期 >= #" & d1 & "# and 开piao日期 < #" & d2 & "# and 数量 <= 0")
    ndr("上月结存_数量") = psum_sl
    ndr("本月收进_数量") = sum_sl_in
    ndr("本月付出_数量") = sum_sl_out
Next