贴段代码给您看看:Public StartTime As String Public EndTime As StringPublic Sub ShowData() Dim cmd As New ADODB.Command Dim rs As New ADODB.Recordset Dim BillsRs As New ADODB.Recordset
DataEnv.rsTEA.Close
cmd.ActiveConnection = SYScnn cmd.CommandType = adCmdText cmd.CommandText = "UPDATE TEA SET STATCOUNT=0" cmd.Execute cmd.CommandText = "UPDATE TEA SET STATCOST=0" cmd.Execute
cmd.CommandText = "SELECT SUM(PRIVATECOST) AS PCOST, SUM(ADDCOST) AS ACOST, SUM(FACTCOST) AS TOTALCOST , SUM(SERVICECOST) AS SCOST" & _ " FROM BILL " & _ " WHERE ETIME>='" & StartTime & "' AND ETIME<='" & EndTime & "'" rs.Open cmd.Execute Me.Sections("Title").Controls("TimeLbl").Caption = StartTime & " ÖÁ " & EndTime Me.Sections("Last").Controls("PrivateLbl").Caption = CStr(Format(CCur(rs.Fields("PCOST")), "£¤#,##0.00")) Me.Sections("Last").Controls("AddLbl").Caption = CStr(Format(CCur(rs.Fields("ACOST")), "£¤#,##0.00")) Me.Sections("Last").Controls("ServiceLbl").Caption = CStr(Format(CCur(rs.Fields("SCOST")), "£¤#,##0.00")) Me.Sections("Last").Controls("TotalLbl").Caption = CStr(Format(CCur(rs.Fields("TOTALCOST")), "×ÜÊÕÈ룺£¤#,##0.00")) rs.Close Set rs = Nothing cmd.CommandText = "SELECT * FROM BILL " & _ " WHERE ETIME>='" & StartTime & "' AND ETIME<='" & EndTime & "'" rs.Open cmd.Execute
BillsRs.Open "BILLS", SYScnn Do Until rs.EOF BillsRs.Filter = "BILLID=" & rs.Fields("ID") If Not BillsRs.EOF Then BillsRs.MoveFirst Do Until BillsRs.EOF cmd.CommandText = "UPDATE TEA SET STATCOUNT=STATCOUNT+" & BillsRs.Fields("AMOUNT") & _ " WHERE ID=" & BillsRs.Fields("TEAID") cmd.Execute cmd.CommandText = "UPDATE TEA SET STATCOST=STATCOST+" & BillsRs.Fields("TOTAL") & _ " WHERE ID=" & BillsRs.Fields("TEAID") cmd.Execute BillsRs.MoveNext Loop rs.MoveNext Loop BillsRs.Close rs.Close DataEnv.TEA DataEnv.rsTEA.Filter = "STATCOUNT > 0" End SubPrivate Sub DataReport_Initialize() DataEnv.TEA End SubPrivate Sub DataReport_Terminate() DataEnv.rsTEA.Close End Sub
Private Sub DataReport_Initialize() DataEnv.TEA End Sub Private Sub DataReport_Terminate() DataEnv.rsTEA.Close End Sub 这段足以,谢谢!!! 我再试试,如果没有问题,下班前加分。
问题是我想两部分分别循环,可细节老是整体循环(见主题)
新建立一个表专门来做Report的DataSource
这样您就可以控制Table的顺序了吧:)
Select * from table1 order by SortColumn
项目名称 项目值
1.ABCD 0.88
2.EFGH 2.33
3.IJKL 0.96
小记:xxx.xx丁丙乙甲:
项目名称 项目值
1.abcd 123.43
2.efgh 8.99
3.ijkl 2.33
4.mnop 0.88
小记:yyy.yy可是一般方法放到报表设计器的“细节”里,老是成了:甲乙丙丁:
项目名称 项目值
1.ABCD 0.88
小记:xxx.xx丁丙乙甲:
项目名称 项目值
1.abcd 123.43
小记:yyy.yy甲乙丙丁:
项目名称 项目值
2.EFGH 22.88
小记:xxx.xx丁丙乙甲:
项目名称 项目值
2.efgh 0.43
小记:yyy.yy甲乙丙丁:
项目名称 项目值
3.IJKL 0.88
小记:xxx.xx丁丙乙甲:
项目名称 项目值
3.ijkl 123.43
小记:yyy.yy
DataEnvironmnet还是ADODC?Dataenv的话再执行一次Command就可以了,记得先关闭
Dataenv.rsCommand1.close
dataenv.Command1
Public EndTime As StringPublic Sub ShowData()
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim BillsRs As New ADODB.Recordset
DataEnv.rsTEA.Close
cmd.ActiveConnection = SYScnn
cmd.CommandType = adCmdText
cmd.CommandText = "UPDATE TEA SET STATCOUNT=0"
cmd.Execute
cmd.CommandText = "UPDATE TEA SET STATCOST=0"
cmd.Execute
cmd.CommandText = "SELECT SUM(PRIVATECOST) AS PCOST, SUM(ADDCOST) AS ACOST, SUM(FACTCOST) AS TOTALCOST , SUM(SERVICECOST) AS SCOST" & _
" FROM BILL " & _
" WHERE ETIME>='" & StartTime & "' AND ETIME<='" & EndTime & "'"
rs.Open cmd.Execute
Me.Sections("Title").Controls("TimeLbl").Caption = StartTime & " ÖÁ " & EndTime
Me.Sections("Last").Controls("PrivateLbl").Caption = CStr(Format(CCur(rs.Fields("PCOST")), "£¤#,##0.00"))
Me.Sections("Last").Controls("AddLbl").Caption = CStr(Format(CCur(rs.Fields("ACOST")), "£¤#,##0.00"))
Me.Sections("Last").Controls("ServiceLbl").Caption = CStr(Format(CCur(rs.Fields("SCOST")), "£¤#,##0.00"))
Me.Sections("Last").Controls("TotalLbl").Caption = CStr(Format(CCur(rs.Fields("TOTALCOST")), "×ÜÊÕÈ룺£¤#,##0.00"))
rs.Close
Set rs = Nothing
cmd.CommandText = "SELECT * FROM BILL " & _
" WHERE ETIME>='" & StartTime & "' AND ETIME<='" & EndTime & "'"
rs.Open cmd.Execute
BillsRs.Open "BILLS", SYScnn
Do Until rs.EOF
BillsRs.Filter = "BILLID=" & rs.Fields("ID")
If Not BillsRs.EOF Then BillsRs.MoveFirst
Do Until BillsRs.EOF
cmd.CommandText = "UPDATE TEA SET STATCOUNT=STATCOUNT+" & BillsRs.Fields("AMOUNT") & _
" WHERE ID=" & BillsRs.Fields("TEAID")
cmd.Execute
cmd.CommandText = "UPDATE TEA SET STATCOST=STATCOST+" & BillsRs.Fields("TOTAL") & _
" WHERE ID=" & BillsRs.Fields("TEAID")
cmd.Execute
BillsRs.MoveNext
Loop
rs.MoveNext
Loop
BillsRs.Close
rs.Close
DataEnv.TEA
DataEnv.rsTEA.Filter = "STATCOUNT > 0"
End SubPrivate Sub DataReport_Initialize()
DataEnv.TEA
End SubPrivate Sub DataReport_Terminate()
DataEnv.rsTEA.Close
End Sub
DataEnv.TEA
End Sub Private Sub DataReport_Terminate()
DataEnv.rsTEA.Close
End Sub 这段足以,谢谢!!!
我再试试,如果没有问题,下班前加分。