我用的控件是F1books,用DAO实现.
Dim i As Integer
Dim Mydb As Database
Dim Myrs1 As Recordset, Myrs2 As Recordset, mrc As Recordset
Set Mydb = OpenDatabase(App.Path + "\store.mdb")
i = 2
F1Book1.ClearRange 2, 1, 65536, 256, F1ClearValues
Set Myrs1 = Mydb.OpenRecordset("select DISTINCT 材料编码 from inlibsearch where 进库日期 >=#" & DTPicker1.Value & "# and 进库日期 <= #" & DTPicker2.Value & "# ")
While Myrs1.EOF = False
Set mrc = Mydb.OpenRecordset("select * from goods where goodsid='" & Myrs1.Fields("材料编码") & "'")
Set Myrs2 = Mydb.OpenRecordset("select sum(数量) as numSum ,sum(金额) as numPrice from inlibsearch where 材料编码='" + mrc.Fields("goodsid") + "' and 进库日期 >=#" & DTPicker1.Value & "# and 进库日期 <= #" & DTPicker2.Value & "# ")
If Not Myrs2.EOF Then
If Not IsNull(Myrs2.Fields("numSum")) Then
F1Book1.TextRC(i, 1) = mrc.Fields("goodsname")
F1Book1.TextRC(i, 2) = mrc.Fields("type")
F1Book1.TextRC(i, 3) = mrc.Fields("unit")
If hasPoint(Myrs2.Fields("numSum")) Then
F1Book1.TextRC(i, 4) = Format(Myrs2.Fields("numSum"), "#0.0")
Else
F1Book1.TextRC(i, 4) = Myrs2.Fields("numSum")
End If
F1Book1.TextRC(i, 6) = Format(Myrs2.Fields("numPrice"), "#0.00")
If F1Book1.TextRC(i, 4) <> 0 Then
F1Book1.TextRC(i, 5) = Format(F1Book1.TextRC(i, 6) / F1Book1.TextRC(i, 4), "#0.00")
Else
F1Book1.TextRC(i, 5) = 0
End If
End If
End If
Myrs2.Close
mrc.Close
Myrs1.MoveNext
i = i + 1
Wend
Myrs1.Close
Set Myrs1 = Mydb.OpenRecordset("select sum(金额) as numPrice from inlibsearch where 进库日期 >=#" & DTPicker1.Value & "# and 进库日期 <= #" & DTPicker2.Value & "# ")
F1Book1.TextRC(i, 1) = "合计"
F1Book1.TextRC(i, 6) = Format(Myrs1.Fields("numPrice"), "#0.00")
Myrs1.Close
Mydb.Close
Dim i As Integer
Dim Mydb As Database
Dim Myrs1 As Recordset, Myrs2 As Recordset, mrc As Recordset
Set Mydb = OpenDatabase(App.Path + "\store.mdb")
i = 2
F1Book1.ClearRange 2, 1, 65536, 256, F1ClearValues
Set Myrs1 = Mydb.OpenRecordset("select DISTINCT 材料编码 from inlibsearch where 进库日期 >=#" & DTPicker1.Value & "# and 进库日期 <= #" & DTPicker2.Value & "# ")
While Myrs1.EOF = False
Set mrc = Mydb.OpenRecordset("select * from goods where goodsid='" & Myrs1.Fields("材料编码") & "'")
Set Myrs2 = Mydb.OpenRecordset("select sum(数量) as numSum ,sum(金额) as numPrice from inlibsearch where 材料编码='" + mrc.Fields("goodsid") + "' and 进库日期 >=#" & DTPicker1.Value & "# and 进库日期 <= #" & DTPicker2.Value & "# ")
If Not Myrs2.EOF Then
If Not IsNull(Myrs2.Fields("numSum")) Then
F1Book1.TextRC(i, 1) = mrc.Fields("goodsname")
F1Book1.TextRC(i, 2) = mrc.Fields("type")
F1Book1.TextRC(i, 3) = mrc.Fields("unit")
If hasPoint(Myrs2.Fields("numSum")) Then
F1Book1.TextRC(i, 4) = Format(Myrs2.Fields("numSum"), "#0.0")
Else
F1Book1.TextRC(i, 4) = Myrs2.Fields("numSum")
End If
F1Book1.TextRC(i, 6) = Format(Myrs2.Fields("numPrice"), "#0.00")
If F1Book1.TextRC(i, 4) <> 0 Then
F1Book1.TextRC(i, 5) = Format(F1Book1.TextRC(i, 6) / F1Book1.TextRC(i, 4), "#0.00")
Else
F1Book1.TextRC(i, 5) = 0
End If
End If
End If
Myrs2.Close
mrc.Close
Myrs1.MoveNext
i = i + 1
Wend
Myrs1.Close
Set Myrs1 = Mydb.OpenRecordset("select sum(金额) as numPrice from inlibsearch where 进库日期 >=#" & DTPicker1.Value & "# and 进库日期 <= #" & DTPicker2.Value & "# ")
F1Book1.TextRC(i, 1) = "合计"
F1Book1.TextRC(i, 6) = Format(Myrs1.Fields("numPrice"), "#0.00")
Myrs1.Close
Mydb.Close
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货