Dim rq As WinForm.DateTimePicker = e.Form.Controls("DateTimePicker1") Tables(e.Form.Name & "_Table1").Fill("Select 公司,发货金额,收款金额,发货金额-iif(收款金额 is null,0,收款金额) As 应收款,[1-30天],[31-60天],[61-90天],[91-180天],[181-360天],[360天以上] from (Select 公司,SUM(发货金额) As 发货金额,(Select sum(收款金额) from {收款} b where a.公司 = b.公司 ) As 收款金额,(Select sum(发货金额) from {发货} d where d.公司 = a.公司 And 发货日期 >= #" & rq.Value.AddDays(-31) & "# And 发货日期 < #" & rq.Value.AddDays(-1) & "# ) As [1-30天],(Select sum(发货金额) from {发货} d where d.公司 = a.公司 And 发货日期 >= #" & rq.Value.AddDays(-61) & "# And 发货日期 < #" & rq.Value.AddDays(-31) & "# ) As [31-60天],(Select sum(发货金额) from {发货} d where d.公司 = a.公司 And 发货日期 >= #" & rq.Value.AddDays(-91) & "# And 发货日期 < #" & rq.Value.AddDays(-61) & "# ) As [61-90天],(Select sum(发货金额) from {发货} d where d.公司 = a.公司 And 发货日期 >= #" & rq.Value.AddDays(-181) & "# And 发货日期 < #" & rq.Value.AddDays(-91) & "# ) As [91-180天],(Select sum(发货金额) from {发货} d where d.公司 = a.公司 And 发货日期 >= #" & rq.Value.AddDays(-361) & "# And 发货日期 < #" & rq.Value.AddDays(-181) & "# ) As [181-360天],(Select sum(发货金额) from {发货} d where d.公司 = a.公司 And 发货日期 < #" & rq.Value.AddDays(-361) & "# ) As [360天以上] from {发货} As a group by 公司) As c where 发货金额-iif(收款金额 is null,0,收款金额) > 0",True)
|