大虾请进!高分赠,在线等
地区销售汇总表
表的结构不能改了
现在要在 货品库、分店资料库和销售库 中查到 指定日期 的货品 在某个分店的 具体销售情况
Private Sub Command3_Click() '地区销售报表
Dim dq_sale_sum(100, 300, 100)
Dim i, j
On Error GoTo errorhandle
Set cnn = New ADODB.Connection
str = "provider=microsoft.jet.oledb.4.0;data source=c:\销售系统.mdb"
cnn.Open str
cnn.CursorLocation = adUseClient
Set rs = New ADODB.Recordset
'''''''''''''''''''''''
str1 = "select * from 分店资料库 order by 分店代码" '分店
rs.Open str1, cnn, adOpenStatic, adLockBatchOptimistic
If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
For numshop = 0 To rs.RecordCount - 1
temp_str = rs.Fields(1).Value
Set rs1 = New ADODB.Recordset '''''''''''''''''''''''''''''
str = "select * from 货品库 order by 货品代号"
rs1.Open str, cnn, adOpenStatic, adLockBatchOptimistic
If rs1.RecordCount > 0 Then
rs1.MoveLast
rs1.MoveFirst
For numgood = 0 To rs1.RecordCount - 1
Set rs2 = New ADODB.Recordset
str = "select * from 销售库 where 分店名称=" & temp_str & " and 日期 between #" & DTP1.Value & "# and #" & DTP2.Value & "# and 货品代码=" & rs1.Fields(0).Value '该分店的销售
rs2.Open str, cnn, adOpenStatic, adLockBatchOptimistic
‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’
在该处,不执行,且无错误提示!5555555555555555
‘’‘’‘’‘’‘’‘’‘’‘’‘’
If rs2.RecordCount > 0 Then
rs2.MoveLast
rs2.MoveFirst
For numl = 0 To rs2.Fields.Count - 1
For numsale = 0 To rs2.RecordCount - 1
dq_sale_sum(numshop, numgood, numl) = dq_sale_sum(numshop, numgood, numl) + rs2.Fields(numl).Value
rs2.MoveNext
Next
Next
End If
rs1.MoveNext
Next
End If
rs.MoveNext
Next
End If
Set xl = New Excel.Application
xl.Workbooks.Open "c:\test.xls"
Set St = xl.ActiveSheet
i = 0
j = 0
St.Cells(1, 1) = "地区销售汇总报表"
St.Cells(2, 1) = "单位:元"
St.Cells(2, 10) = "统计日期:" & DTP1.Value & " " & DTP2.Value
St.Cells(3, 1) = "货品名称"
St.Cells(3, 2) = "数量"
St.Cells(3, 3) = "金额"
rs1.MoveLast
rs1.MoveFirst
For i = 0 To rs1.RecordCount - 1
rs.MoveLast
rs.MoveFirst
For j = 0 To rs1.Fields.Count - 1
St.Cells(3, 4 + j) = rs1.Fields(j + 1).Name
St.Cells(4, 4 + j) = "数量"
St.Cells(4, 5 + j) = "金额"
St.Cells(i + 5, j + 4) = dq_sale_sum(j, i, 3) '每个分店的数量和金额
St.Cells(i + 5, 5 + j) = dq_sale_sum(j, i, 4)
St.Cells(i + 3, 2) = St.Cells(i + 3, 2) + St.Cells(i + 5, j + 4) '合计数量和金额
St.Cells(i + 3, 3) = St.Cells(i + 3, 3) + St.Cells(i + 5, j + 5)
If j >= 6 Then
St.Cells(i + 3, 4 + rs1.Fields.Count + j - 6) = St.Cells(i + 3, 4 + rs1.Fields.Count + j - 6) + dq_sale_sum(j, i, j)
End If
Next j
rs.MoveNext
Next i
xl.Visible = True
Set xl = Nothing
Set St = Nothing
errorhandle:
End Sub