这是一段关于报表的代码,我想知道报表生成的方法,以及在一个程序中是怎样执行的,
strSQL = "select loanid From rrep5class where month=" & SetStrV(dEndDate) & "and loanid not in" _
& "(select loanid from rrep5class where month=" & SetStrV(dStartDate) & ") into temp tmploanid"
Cn.OpenResultset (strSQL)
strSQL = "create unique index idx_tmploanid on tmploanid(loanid)"
Cn.OpenResultset (strSQL)
strSQL = "select sum(case when level=1 then abal+bbal+cbal else 0 end)," _
& "sum(case when level=2 then abal+bbal+cbal else 0 end)," _
& "sum(case when level=3 then abal+bbal+cbal else 0 end)," _
& "sum(case when level=4 then abal+bbal+cbal else 0 end)," _
& "sum(case when level=5 then abal+bbal+cbal else 0 end) " _
& "from rrep5class a, tmploanid b " _
& "where a.loanid=b.loanid and a.month=" & SetStrV(dEndDate)
Set Rs = Cn.OpenResultset(strSQL)
If Not Rs.EOF Then
repData.Cells(7, 4) = Format(IIf(IsNull(Rs(0)), 0, Rs(0)), "###0.00")
repData.Cells(7, 5) = Format(IIf(IsNull(Rs(1)), 0, Rs(1)), "###0.00")
repData.Cells(7, 6) = Format(IIf(IsNull(Rs(2)), 0, Rs(2)), "###0.00")
repData.Cells(7, 7) = Format(IIf(IsNull(Rs(3)), 0, Rs(3)), "###0.00")
repData.Cells(7, 8) = Format(IIf(IsNull(Rs(4)), 0, Rs(4)), "###0.00")
repData.Cells(7, 9) = Format(CDbl(repData.Cells(7, 4)) + CDbl(repData.Cells(7, 5)) + CDbl(repData.Cells(7, 6)) + CDbl(repData.Cells(7, 7)) + CDbl(repData.Cells(7, 8)), "###0.00")
End If
strSQL = "select loanid From rrep5class where month=" & SetStrV(dEndDate) & "and loanid not in" _
& "(select loanid from rrep5class where month=" & SetStrV(dStartDate) & ") into temp tmploanid"
Cn.OpenResultset (strSQL)
strSQL = "create unique index idx_tmploanid on tmploanid(loanid)"
Cn.OpenResultset (strSQL)
strSQL = "select sum(case when level=1 then abal+bbal+cbal else 0 end)," _
& "sum(case when level=2 then abal+bbal+cbal else 0 end)," _
& "sum(case when level=3 then abal+bbal+cbal else 0 end)," _
& "sum(case when level=4 then abal+bbal+cbal else 0 end)," _
& "sum(case when level=5 then abal+bbal+cbal else 0 end) " _
& "from rrep5class a, tmploanid b " _
& "where a.loanid=b.loanid and a.month=" & SetStrV(dEndDate)
Set Rs = Cn.OpenResultset(strSQL)
If Not Rs.EOF Then
repData.Cells(7, 4) = Format(IIf(IsNull(Rs(0)), 0, Rs(0)), "###0.00")
repData.Cells(7, 5) = Format(IIf(IsNull(Rs(1)), 0, Rs(1)), "###0.00")
repData.Cells(7, 6) = Format(IIf(IsNull(Rs(2)), 0, Rs(2)), "###0.00")
repData.Cells(7, 7) = Format(IIf(IsNull(Rs(3)), 0, Rs(3)), "###0.00")
repData.Cells(7, 8) = Format(IIf(IsNull(Rs(4)), 0, Rs(4)), "###0.00")
repData.Cells(7, 9) = Format(CDbl(repData.Cells(7, 4)) + CDbl(repData.Cells(7, 5)) + CDbl(repData.Cells(7, 6)) + CDbl(repData.Cells(7, 7)) + CDbl(repData.Cells(7, 8)), "###0.00")
End If
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货