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

以文本方式查看主题

-  Foxtable(狐表)  (http://www.foxtable.com/bbs/index.asp)
--  专家坐堂  (http://www.foxtable.com/bbs/list.asp?boardid=2)
----  统计数据代码出错问题  (http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&id=96135)

--  作者：feixianzhi
--  发布时间：2017/2/14 8:46:00
--  统计数据代码出错问题

For Each dr As DataRow In DataTables("合同表").Select("")
    \'第一季度应返数量
    Dim dt1 As DataTable=DataTables("批发表")
    Dim dt2 As DataTable=DataTables("入库表")

    Dim y As Integer = 2016 \'指定年份
    Dim q As Integer = 1 \'指定季度
    Dim dt3 As New Date(y, 3 * (q - 1) + 1, 1) \'获取该季度的第一天
    Dim dt4 As New Date(y, 3 * q, Date.DaysInMonth(y,3 * q)) \'获取该季度的最后一天
    Dim Filter As String
    Filter = "日期 >= #" & dt3 & "# And 日期 <= #" & dt4 & "#"

    Dim YFSL1 As Double = dt1.Compute("sum(数量)", "高开编号 = \'" & dr("财务编号") & "\'","Filter")
    Dim YFSL2 As Double = dt1.Compute("sum(数量)", "返利编号 = \'" & dr("财务编号") & "\'","Filter")
    Dim YFSL3 As Double = dt2.Compute("sum(数量)", "高开编号 = \'" & dr("财务编号") & "\'","Filter")
    Dim YFSL4 As Double = dt2.Compute("sum(数量)", "返利编号 = \'" & dr("财务编号") & "\'","Filter")
    dr("第一季度应返数量") = YFSL1 + YFSL2+ YFSL3+ YFSL4

    \'第一季度应返金额
    Dim YFJE1 As Double = dt1.Compute("sum(返利应返金额)", "返利编号 = \'" & dr("财务编号") & "\'",Filter)
    Dim YFJE2 As Double = dt1.Compute("sum(高开应返金额)", "高开编号 = \'" & dr("财务编号") & "\'",Filter)
    Dim YFJE3 As Double = dt2.Compute("sum(返利应返金额)", "返利编号 = \'" & dr("财务编号") & "\'",Filter)
    Dim YFJE4 As Double = dt2.Compute("sum(高开应返金额)", "高开编号 = \'" & dr("财务编号") & "\'",Filter)
    dr("第一季度应返金额") = YFJE1 + YFJE2+ YFJE3+ YFJE4

Next

以上代码，我想统计第一季度数据，结果出错，提示如下：

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

请老师帮忙改一下。

--  作者：有点蓝
--  发布时间：2017/2/14 9:03:00
--
Dim YFSL1 As Double = dt1.Compute("sum(数量)", "高开编号 = \'" & dr("财务编号") & "\' and " & Filter)

其它Compute参考自己改改

--  作者：有点蓝
--  发布时间：2017/2/14 9:05:00
--
或者

Dim YFSL1 As Double = dt1.SqlCompute("sum(数量)", "高开编号 = \'" & dr("财务编号") & "\' and month(日期) in (1,2,3)" )

--  作者：feixianzhi
--  发布时间：2017/2/14 10:07:00
--

For Each dr As DataRow In DataTables("合同表").Select("")
Dim dt1 As DataTable=DataTables("批发表")
Dim dt2 As DataTable=DataTables("入库表")
Dim dt3 As DataTable=DataTables("合同表")
Dim dt4 As DataTable=DataTables("收缴表")

\'第一季度应返数量
Dim YFSL1 As Double = dt1.SqlCompute("sum(数量)", "高开编号 = \'" & dr("财务编号") & "\' and month(单据日期) in (1,2,3)" )
Dim YFSL2 As Double = dt1.SqlCompute("sum(数量)", "返利编号 = \'" & dr("财务编号") & "\' and month(单据日期) in (1,2,3)" )
Dim YFSL3 As Double = dt2.SqlCompute("sum(数量)", "高开编号 = \'" & dr("财务编号") & "\' and month(单据日期) in (1,2,3)" )
Dim YFSL4 As Double = dt2.SqlCompute("sum(数量)", "返利编号 = \'" & dr("财务编号") & "\' and month(单据日期) in (1,2,3)" )
dr("第一季度应返数量") = YFSL1 + YFSL2+ YFSL3+ YFSL4

\'第一季度应返金额
Dim YFJE1 As Double = dt1.SqlCompute("sum(返利应返金额)", "返利编号 = \'" & dr("财务编号") &"\' And month(单据日期) In (1,2,3)" )
Dim YFJE2 As Double = dt1.SqlCompute("sum(高开应返金额)", "高开编号 = \'" & dr("财务编号") & "\' and month(单据日期) in (1,2,3)" )
Dim YFJE3 As Double = dt2.SqlCompute("sum(返利应返金额)", "返利编号 = \'" & dr("财务编号") & "\' and month(单据日期) in (1,2,3)" )
Dim YFJE4 As Double = dt2.SqlCompute("sum(高开应返金额)", "高开编号 = \'" & dr("财务编号") & "\' and month(单据日期) in (1,2,3)" )
dr("第一季度应返金额") = YFJE1 + YFJE2+ YFJE3+ YFJE4


Next

我这是统计第一季度数据，现在合同表里有录入时间一列，我想重新改代码，加入，录入时间在第一季度的合同才统计以上数据，怎么改，请有点蓝老师帮忙。

--  作者：feixianzhi
--  发布时间：2017/2/14 10:25:00
--
请有点蓝老师帮忙呀。

--  作者：有点蓝
--  发布时间：2017/2/14 10:44:00
--
month(录入时间) in (1,2,3)

SqlCompute的效率也不高，数据多的话可能会很慢。上传例子看看，能不能有其他方法统计

--  作者：feixianzhi
--  发布时间：2017/2/14 10:48:00
--

下载信息  [文件大小：   下载次数： ]
点击浏览该文件:管理项目9.rar

麻烦有点蓝老师了，正常我的所有表都是外部数据表，而且批发及和入库表都有40万条数据。

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

参考代码

For Each dr As DataRow In DataTables("合同表").Select("")
    Dim y As Integer = cdate(dr("录入时间")).year \'指定年份
    Dim q As Integer = Math.Ceiling(cdate(dr("录入时间")).Month / 3) \'指定季度

    If q = 1 Then
        Dim dt1 As DataTable=DataTables("批发表")
        Dim dt2 As DataTable=DataTables("入库表")

        Dim dt3 As New Date(y, 3 * (q - 1) + 1, 1) \'获取该季度的第一天
        Dim dt4 As Date = dt3.AddMonths(1)
        Dim Filter As String
        Filter = "单据日期 >= #" & dt3 & "# And 单据日期 < #" & dt4 & "#"

        Dim YFSL1 As Double = dt1.Compute("sum(数量)", "高开编号 = \'" & dr("财务编号") & "\' and " & Filter)
        Dim YFSL2 As Double = dt1.Compute("sum(数量)", "返利编号 = \'" & dr("财务编号") & "\' and " & Filter)
        Dim YFSL3 As Double = dt2.Compute("sum(数量)", "高开编号 = \'" & dr("财务编号") & "\' and " & Filter)
        Dim YFSL4 As Double = dt2.Compute("sum(数量)", "返利编号 = \'" & dr("财务编号") & "\' and " & Filter)
        dr("第一季度应返数量") = YFSL1 + YFSL2+ YFSL3+ YFSL4
    End If
Next

--  作者：feixianzhi
--  发布时间：2017/2/14 14:20:00
--

For Each dr As DataRow In DataTables("合同表").Select("")
Dim dt1 As DataTable=DataTables("批发表")
Dim dt2 As DataTable=DataTables("入库表")
Dim y As Integer = cdate(dr("录入时间")).year \'指定年份
Dim q As Integer = Math.Ceiling(cdate(dr("录入时间")).Month / 3) \'指定季度

\'第一季度应返数量
Dim YFSL1 As Double = dt1.SqlCompute("sum(数量)", "高开编号 = \'" & dr("财务编号") & "\' and month(单据日期) in (1,2,3)" )
Dim YFSL2 As Double = dt1.SqlCompute("sum(数量)", "返利编号 = \'" & dr("财务编号") & "\' and month(单据日期) in (1,2,3)" )
Dim YFSL3 As Double = dt2.SqlCompute("sum(数量)", "高开编号 = \'" & dr("财务编号") & "\' and month(单据日期) in (1,2,3)" )
Dim YFSL4 As Double = dt2.SqlCompute("sum(数量)", "返利编号 = \'" & dr("财务编号") & "\' and month(单据日期) in (1,2,3)" )
dr("第一季度应返数量") = YFSL1 + YFSL2+ YFSL3+ YFSL4

\'第一季度应返金额
Dim YFJE1 As Double = dt1.SqlCompute("sum(返利应返金额)", "返利编号 = \'" & dr("财务编号") &"\' And month(单据日期) In (1,2,3)" )
Dim YFJE2 As Double = dt1.SqlCompute("sum(高开应返金额)", "高开编号 = \'" & dr("财务编号") & "\' and month(单据日期) in (1,2,3)" )
Dim YFJE3 As Double = dt2.SqlCompute("sum(返利应返金额)", "返利编号 = \'" & dr("财务编号") & "\' and month(单据日期) in (1,2,3)" )
Dim YFJE4 As Double = dt2.SqlCompute("sum(高开应返金额)", "高开编号 = \'" & dr("财务编号") & "\' and month(单据日期) in (1,2,3)" )
dr("第一季度应返金额") = YFJE1 + YFJE2+ YFJE3+ YFJE4

\'以下代码是计算第一季度计提数量和金额
If q = 1 Then
Dim dt3 As New Date(y, 3 * (q - 1) + 1, 1) \'获取该季度的第一天
Dim dt4 As Date = dt3.AddMonths(1)
Dim Filter As String
Filter = "单据日期 >= #" & dt3 & "# And 单据日期 < #" & dt4 & "#"

Dim JTSL1 As Double = dt1.Compute("sum(数量)", "高开编号 = \'" & dr("财务编号") & "\' and " & Filter)
Dim JTSL2 As Double = dt1.Compute("sum(数量)", "返利编号 = \'" & dr("财务编号") & "\' and " & Filter)
Dim JTSL3 As Double = dt2.Compute("sum(数量)", "高开编号 = \'" & dr("财务编号") & "\' and " & Filter)
Dim JTSL4 As Double = dt2.Compute("sum(数量)", "返利编号 = \'" & dr("财务编号") & "\' and " & Filter)
dr("第一季度计提数量") = JTSL1 + JTSL2+ JTSL3+ JTSL4
End If
Next

有点色老师，我的代码如下，但第一季度计提数量全部是零。我分析可能是Filter的取值范围的问题，我想问一下：
Filter = "单据日期 >= #" & dt3 & "# And 单据日期 < #" & dt4 & "#"
这句的取值范围是第一季度吗，也就是说，日期为1月1日至3月31日吗？

--  作者：feixianzhi
--  发布时间：2017/2/14 14:29:00
--
找出问题了，是录入的时间为2016年，而单据日期为2017年，所以没有统计数量。