Sub OnClick(ByVal Item)
Dim i, n, k, m, t, e, f, n1
Dim Sql, oCom, conn, oCom1
Dim j, a, b, c, d, z, sql1, a1, b1, c1
Dim ylp, wdp, llp, ylx, wdx, llx, yld, wdd, lld
Dim zlp, dyp, sdp, zlx, dyx, sdx, zld, dyd, sdd
Dim strcn, cn
Dim is_SQL
Dim oRs, oRs1
Dim By, Bm, Bd
Dim Ny, Nm, Nd
Dim MSFlexGrid2,Text2,Byear,Eyear,Bmon,Emon
Dim BeginDate, EndDate
Set Byear = ScreenItems(“Byear”)
Set Eyear = ScreenItems(“Eyear”)
Set Bmon = ScreenItems(“Bmon”)
Set Emon = ScreenItems(“Emon”)
Set Text2 = ScreenItems(“Text2”)
Set MSFlexGrid2 = ScreenItems(“MSFlexGrid2”)
BeginDate = Byear.Text & “-” & Bmon.Text
EndDate = Eyear.Text & “-” & Emon.Text
If Byear.Text > Eyear.Text Or Byear.Text = Eyear.Text And Bmon.Text > Emon.Text Then
MsgBox “输入的时间不正确”, vbOK, “错误的起始时间”
End If
Sql = “select convert(char(10),[riqi],120),avg(yali),avg(wendu),avg(liuliang), avg(zhongliang),avg(dianya),avg(sudu) from ribao where convert(char(7),[riqi],120) between '” & BeginDate & “’ and’” & EndDate & “’ group by convert(char(10),[riqi],120) order by 1”
sql1 = “select avg(yali)as ylp,avg(wendu)as wdp,avg(liuliang)as llp,avg(zhongliang)as zlp,avg(dianya)as dyp,avg(sudu)as sdp,min(yali)as ylx,min(wendu)as wdx,min(liuliang)As llx,min(zhongliang)As zlx,min(dianya)As dyx,min(sudu)As sdx,max(yali)As yld,max(wendu)As wdd,max(liuliang)As lld,max(zhongliang)As zld,max(dianya)As dyd,max(sudu)As sdd from (select top 100 percent convert(char(10) ,[riqi],120) As riqi,avg(yali) As yali ,avg(wendu) As wendu ,avg(liuliang) As liuliang ,avg(zhongliang) As zhongliang ,avg(dianya) As dianya ,avg(sudu) As sudu from ribao where convert(char(7),[riqi],120) between '” & BeginDate & “’ and’” & EndDate & “'group by convert(char(10),[riqi],120) order by 1) As t”
strcn = “Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist SecurityInfo=False;Initial Catalog=baobiao1;Data Source=.\wincc”
Set conn = CreateObject(“ADODB.Connection”)
conn.ConnectionString = strcn
conn.CursorLocation = 3
conn.Open
'使用命令文本查询
Set oRs = CreateObject(“ADODB.Recordset”)
Set oCom = CreateObject("ADODB.Command")
oCom.CommandType = 1
Set oCom.ActiveConnection = conn
oCom.CommandText = Sql
Set oRs = oCom.Execute
n = oRs.RecordCount
Text2.Text = n
Set oCom1 = CreateObject(“ADODB.Command”)
oCom1.CommandType = 1
Set oCom1.ActiveConnection = conn
oCom1.CommandText = sql1
Set oRs1 = oCom1.Execute
n1 = oRs1.RecordCount
ylp = oRs1(“ylp”): wdp = oRs1(“wdp”): llp = oRs1(“llp”): ylx = oRs1(“ylx”): wdx = oRs1(“wdx”): llx = oRs1(“llx”): yld = oRs1(“yld”): wdd = oRs1(“wdd”): lld = oRs1(“lld”)
zlp = oRs1(“zlp”): dyp = oRs1(“dyp”): sdp = oRs1(“sdp”): zlx = oRs1(“zlx”): dyx = oRs1(“dyx”): sdx = oRs1(“sdx”): zld = oRs1(“zld”): dyd = oRs1(“dyd”): sdd = oRs1(“sdd”)
If n = 0 Then
MsgBox “对不起,没有找到符合条件的数据”, vbOK, “没有相关数据”
End If
oRs.Requery
MSFlexGrid2.Clear
MSFlexGrid2.Rows = oRs.RecordCount + 6
MSFlexGrid2.ColWidth(0) = 900
MSFlexGrid2.ColWidth(1) = 1600
MSFlexGrid2.ColWidth(2) = 1000
MSFlexGrid2.ColWidth(3) = 1000
MSFlexGrid2.ColWidth(4) = 1000
MSFlexGrid2.ColWidth(5) = 1000
MSFlexGrid2.ColWidth(6) = 1000
MSFlexGrid2.ColWidth(7) = 1000
MSFlexGrid2.Row = 0
For z = 0 To 7
MSFlexGrid2.Col = z
MSFlexGrid2.Text = “R980履带式布料机月报表”
Next
MSFlexGrid2.MergeCells = 4
MSFlexGrid2.MergeRow(0) = True
MSFlexGrid2.TextMatrix(1, 0) = “编号”
MSFlexGrid2.TextMatrix(1, 1) = “日期”
MSFlexGrid2.TextMatrix(1, 2) = “压力”
MSFlexGrid2.TextMatrix(1, 3) = “温度”
MSFlexGrid2.TextMatrix(1, 4) = “流量”
MSFlexGrid2.TextMatrix(1, 5) = “重量”
MSFlexGrid2.TextMatrix(1, 6) = “电压”
MSFlexGrid2.TextMatrix(1, 7) = “速度”
MSFlexGrid2.TextMatrix(oRs.RecordCount + 3, 0) = "最大值"
MSFlexGrid2.TextMatrix(oRs.RecordCount + 4, 0) = "最小值"
MSFlexGrid2.TextMatrix(oRs.RecordCount + 5, 0) = "平均值"
MSFlexGrid2.ColAlignment(0) = 4
MSFlexGrid2.ColAlignment(1) = 4
MSFlexGrid2.ColAlignment(2) = 4
MSFlexGrid2.ColAlignment(3) = 4
MSFlexGrid2.ColAlignment(4) = 4
MSFlexGrid2.ColAlignment(5) = 4
MSFlexGrid2.ColAlignment(6) = 4
MSFlexGrid2.ColAlignment(7) = 4
For i = 1 To oRs.RecordCount
MSFlexGrid2.TextMatrix(i + 1, 0) = i
Next
If (n > 0) Then
oRs.MoveFirst
i = 0
Do While Not oRs.EOF
n = n + 1
yld = Int(yld * 10 ^ 3 + 0.5) / (10 ^ 3)
wdd = Int(wdd * 10 ^ 3 + 0.5) / (10 ^ 3)
lld = Int(lld * 10 ^ 3 + 0.5) / (10 ^ 3)
zld = Int(zld * 10 ^ 3 + 0.5) / (10 ^ 3)
dyd = Int(dyd * 10 ^ 3 + 0.5) / (10 ^ 3)
sdd = Int(sdd * 10 ^ 3 + 0.5) / (10 ^ 3)
zlx = Int(zlx * 10 ^ 3 + 0.5) / (10 ^ 3)
dyx = Int(dyx * 10 ^ 3 + 0.5) / (10 ^ 3)
sdx = Int(sdx * 10 ^ 3 + 0.5) / (10 ^ 3)
ylx = Int(ylx * 10 ^ 3 + 0.5) / (10 ^ 3)
wdx = Int(wdx * 10 ^ 3 + 0.5) / (10 ^ 3)
llx = Int(llx * 10 ^ 3 + 0.5) / (10 ^ 3)
ylp = Int(ylp * 10 ^ 3 + 0.5) / (10 ^ 3)
wdp = Int(wdp * 10 ^ 3 + 0.5) / (10 ^ 3)
llp = Int(llp * 10 ^ 3 + 0.5) / (10 ^ 3)
zlp = Int(zlp * 10 ^ 3 + 0.5) / (10 ^ 3)
dyp = Int(dyp * 10 ^ 3 + 0.5) / (10 ^ 3)
sdp = Int(sdp * 10 ^ 3 + 0.5) / (10 ^ 3)
i = i + 1
a = CStr(oRs.Fields(1).Value)
a = Int(a * 10 ^ 3 + 0.5) / (10 ^ 3)
b = CStr(oRs.Fields(2).Value)
b = Int(b * 10 ^ 3 + 0.5) / (10 ^ 3)
c = CStr(oRs.Fields(3).Value)
c = Int(c * 10 ^ 3 + 0.5) / (10 ^ 3)
'*****************************
a1 = CStr(oRs.Fields(4).Value)
a1 = Int(a1 * 10 ^ 3 + 0.5) / (10 ^ 3)
b1 = CStr(oRs.Fields(5).Value)
b1 = Int(b1 * 10 ^ 3 + 0.5) / (10 ^ 3)
c1 = CStr(oRs.Fields(6).Value)
c1 = Int(c1 * 10 ^ 3 + 0.5) / (10 ^ 3)
'********************************
MSFlexGrid2.TextMatrix(i + 1, 1) = CStr(oRs.Fields(0).Value)
MSFlexGrid2.TextMatrix(i + 1, 2) = a
MSFlexGrid2.TextMatrix(i + 1, 3) = b
MSFlexGrid2.TextMatrix(i + 1, 4) = c
MSFlexGrid2.TextMatrix(i + 1, 5) = a1
MSFlexGrid2.TextMatrix(i + 1, 6) = b1
MSFlexGrid2.TextMatrix(i + 1, 7) = c1
MSFlexGrid2.TextMatrix(oRs.RecordCount + 3, 2) = yld
MSFlexGrid2.TextMatrix(oRs.RecordCount + 4, 2) = ylx
MSFlexGrid2.TextMatrix(oRs.RecordCount + 5, 2) = ylp
MSFlexGrid2.TextMatrix(oRs.RecordCount + 3, 3) = wdd
MSFlexGrid2.TextMatrix(oRs.RecordCount + 4, 3) = wdx
MSFlexGrid2.TextMatrix(oRs.RecordCount + 5, 3) = wdp
MSFlexGrid2.TextMatrix(oRs.RecordCount + 3, 4) = lld
MSFlexGrid2.TextMatrix(oRs.RecordCount + 4, 4) = llx
MSFlexGrid2.TextMatrix(oRs.RecordCount + 5, 4) = llp
'*********************************************
MSFlexGrid2.TextMatrix(oRs.RecordCount + 3, 5) = zld
MSFlexGrid2.TextMatrix(oRs.RecordCount + 4, 5) = zlx
MSFlexGrid2.TextMatrix(oRs.RecordCount + 5, 5) = zlp
MSFlexGrid2.TextMatrix(oRs.RecordCount + 3, 6) = dyd
MSFlexGrid2.TextMatrix(oRs.RecordCount + 4, 6) = dyx
MSFlexGrid2.TextMatrix(oRs.RecordCount + 5, 6) = dyp
MSFlexGrid2.TextMatrix(oRs.RecordCount + 3, 7) = sdd
MSFlexGrid2.TextMatrix(oRs.RecordCount + 4, 7) = sdx
MSFlexGrid2.TextMatrix(oRs.RecordCount + 5, 7) = sdp
oRs.MoveNext
Loop
End If
oRs.close’这是后加的,退出程序前,关闭与数据库,记录集的连接
ors1.close’这是后加的
conn.close’这是后加的
End Sub