sCon= "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=baobiao1;Data Source=.\WINCC"

strSQL1= "SELECT CONVERT(char(13), Mytime, 20) AS Mytime, Data01, Data02 , Data03, Data04, Data05, Data06, Data07, Data08 FROM  [biao2] where month(MyTime)='"& Bm &"' and year(MyTime)='"& By & "' and day(MyTime)='"& Bd &"'ORDER BY MyTime;" 

''MsgBox strSQL1 

strSQL2="select sum(Data01),sum(Data02),sum(Data03),sum(Data04),sum(Data05),sum(Data06),sum(Data07),sum(Data08) from biao2 where month(MyTime)='"& Bm &"' and year(MyTime)='"& By & "' and day(MyTime)='"& Bd &"' ;"  

strSQL3="select avg(Data01),avg(Data02),avg(Data03),avg(Data04),avg(Data05),avg(Data06),avg(Data07),avg(Data08) from biao2 where month(MyTime)='"& Bm &"' and year(MyTime)='"& By & "' and day(MyTime)='"& Bd &"' ;" 

'strSQL3="select avg(Date01),avg(Date02),avg(Date03),avg(Date04),avg(Date05),avg(Date06),avg(Date07),avg(Date08) from ReportDay where month(MyTime)='"& Bm &"' and year(MyTime)='"& By & "' and day(MyTime)='"& Bd &"' ;" ReportDay

Set conn=CreateObject("ADODB.Connection")

    conn.ConnectionString = sCon

    conn.CursorLocation = 3  

    conn.Open

Set oRs1 = CreateObject("ADODB.Recordset")

Set oCom = CreateObject("ADODB.Command")

    oCom.CommandType = 1

Set oCom.ActiveConnection = conn

    oCom.CommandText = strSQL1

Set oRs1 = oCom.Execute              '获取查询的历史数据集，是虚拟的数据结果表

    m = oRs1.RecordCount

    Text2.Text = m 

    oCom.CommandText = strSQL2

Set oRs2 = oCom.Execute              '获取查询的合计数据集，是虚拟的数据结果表

    oCom.CommandText = strSQL3

Set oRs3 = oCom.Execute              '获取查询的平均数据集，是虚拟的数据结果表

''            获取查询的平均数据集，是虚拟的数据结果表

'MsgBox oRs3  

'表格初始化，获取窗体上的报表控件名称，如果是自己添加一定要对应名称  

Dim olist

Set olist = ScreenItems("MSFlexGrid1")

    olist.clear

    olist.Cols=10

    olist.Rows =m+3

    '列内容居中显示

    olist.ColAlignment(0)=3

    olist.ColAlignment(1)=3

    olist.ColAlignment(2)=3

    olist.ColAlignment(3)=3

    olist.ColAlignment(4)=3

    olist.ColAlignment(5)=3

    olist.ColAlignment(6)=3

    olist.ColAlignment(7)=3

    olist.ColAlignment(8)=3

    olist.ColAlignment(9)=3

    '设置列宽

    olist.ColWidth(0) = 800                 

    olist.ColWidth(1) = 1900

    olist.ColWidth(2) = 1700

    olist.ColWidth(3) = 1700

    olist.ColWidth(4) = 1700

    olist.ColWidth(5) = 1700

    olist.ColWidth(6) = 1700

    olist.ColWidth(7) = 1700

    olist.ColWidth(8) = 1700

    olist.ColWidth(9) = 1700

    '设置标头

    oList.TextMatrix(0, 0) = "编号"

    oList.TextMatrix(0, 1) = "日期"

    oList.TextMatrix(0, 2) = "变量1"

    oList.TextMatrix(0, 3) = "变量2"

    oList.TextMatrix(0, 4) = "变量3"

    oList.TextMatrix(0, 5) = "变量4"

    oList.TextMatrix(0, 6) = "变量5"

    oList.TextMatrix(0, 7) = "变量6"

    oList.TextMatrix(0, 8) = "变量7"

    oList.TextMatrix(0, 9) = "变量8"

Dim sum1    

Dim i

i=0

'如果查询不到数据，提示没有符合条件的记录，如果有数据，把数据写入报表控件

If (oRs1.EOF) Then

MsgBox("没有符合条件的记录")

Else

'循环把查询数据到的数据，一行一行写入报表控件

While  i<(m)

i=i+1

oList.TextMatrix(i, 0) = CStr(i)

oList.TextMatrix(i, 1) = CStr(oRs1.Fields(0).Value)

oList.TextMatrix(i, 2) = FormatNumber(oRs1.Fields(1).Value,2) '保留两位小数

oList.TextMatrix(i, 3) = FormatNumber(oRs1.Fields(2).Value,2) '保留两位小数

oList.TextMatrix(i, 4) = FormatNumber(oRs1.Fields(3).Value,2) '保留两位小数

oList.TextMatrix(i, 5) = FormatNumber(oRs1.Fields(4).Value,2) '保留两位小数

oList.TextMatrix(i, 6) = FormatNumber(oRs1.Fields(5).Value,2) '保留两位小数

oList.TextMatrix(i, 7) = FormatNumber(oRs1.Fields(6).Value,2) '保留两位小数

oList.TextMatrix(i, 8) = FormatNumber(oRs1.Fields(7).Value,2) '保留两位小数

oList.TextMatrix(i, 9) = FormatNumber(oRs1.Fields(8).Value,2) '保留两位小数

' MsgBox i

oRs1.MoveNext   '移到下一行

Wend

'合计内容，显示到最后一行

    oList.TextMatrix(m+1, 0) = "合计"

    oList.TextMatrix(m+1, 1) = "产品型号1"

    oList.TextMatrix(m+1, 2) = "产品型号2"

    oList.TextMatrix(m+1, 3) = "产品型号3" 

    oList.TextMatrix(m+1, 4) = "产品型号4" 

    oList.TextMatrix(m+1, 5) = "产品型号5" 

    oList.TextMatrix(m+1, 6) = "产品型号6" 

    oList.TextMatrix(m+1, 7) = "产品型号7" 

    oList.TextMatrix(m+1, 8) = "产品型号8" 

    oList.TextMatrix(m+1, 9) = "产品型号9" 

    '平均内容，显示到最后一行

    oList.TextMatrix(m+2, 0) = "数量"

    oList.TextMatrix(m+2, 1) = CStr(Bm)& "月"&CStr(Bm)& "日"

    oList.TextMatrix(m+2, 2) = FormatNumber(oRs3.Fields(0).Value,2) '保留两位小数

    oList.TextMatrix(m+2, 3) = FormatNumber(oRs3.Fields(1).Value,2) '保留两位小数

    oList.TextMatrix(m+2, 4) = FormatNumber(oRs3.Fields(2).Value,2) '保留两位小数

    oList.TextMatrix(m+2, 5) = FormatNumber(oRs3.Fields(3).Value,2) '保留两位小数

    oList.TextMatrix(m+2, 6) = FormatNumber(oRs3.Fields(4).Value,2) '保留两位小数

    oList.TextMatrix(m+2, 7) = FormatNumber(oRs3.Fields(5).Value,2) '保留两位小数

    oList.TextMatrix(m+2, 8) = FormatNumber(oRs3.Fields(6).Value,2) '保留两位小数

    oList.TextMatrix(m+2, 9) = FormatNumber(oRs3.Fields(7).Value,2) '保留两位小数

End If  

'sum = Val(oList.TextMatrix(1, 2)) + Val(oList.TextMatrix(2, 2))

Set oRs1 = Nothing

Set oRs2 = Nothing

Set oRs3 = Nothing

Set oCom = Nothing

conn.Close

Set conn = Nothing