Private Sub Form_Load()
Dim sql As String
Select Case summary_menu
Case "check" '按货物名称汇总
Label1.Caption = "按货物名称汇总"
sql = "select 货源地,sum(金额) as 总金额 from 货物详况 group by 货源地 order by sum(金额)"
rs_sum.CursorLocation = adUseClient
rs_sum.Open sql, conn, adOpenKeyset, adLockPessimistic
addup = 0
MSFlexGrid1.Rows = rs_sum.RecordCount + 2
MSFlexGrid1.Cols = 3
' 设置表头
MSFlexGrid1.Row = 0
MSFlexGrid1.Col = 0
MSFlexGrid1.Text = "货源地"
MSFlexGrid1.Col = 1
MSFlexGrid1.Text = "总金额"
MSFlexGrid1.Col = 2
MSFlexGrid1.Text = "入出库"
If rs_sum.EOF = False Then
rs_sum.MoveFirst
Do While Not rs_sum.EOF
MSFlexGrid1.Row = MSFlexGrid1.Row + 1
MSFlexGrid1.Col = 0
MSFlexGrid1.Text = rs_sum.Fields(0)
MSFlexGrid1.Col = 1
If CDbl(rs_sum.Fields(1)) < 0 Then
MSFlexGrid1.Text = Replace(rs_sum.Fields(1), "-", "")
MSFlexGrid1.Col = 2
MSFlexGrid1.Text = "出库"
Else
MSFlexGrid1.Text = rs_sum.Fields(1)
MSFlexGrid1.Col = 2
MSFlexGrid1.Text = "入库"
End If
addup = addup + CDbl(rs_sum.Fields(1))
rs_sum.MoveNext
Loop
MSFlexGrid1.Row = MSFlexGrid1.Row + 1
MSFlexGrid1.Col = 0
MSFlexGrid1.Text = "(总计)"
MSFlexGrid1.Col = 1
MSFlexGrid1.Text = addup
End If
rs_sum.Close
Case "date" '按日期汇总
Label1.Caption = "按日期汇总"
sql = "select 日期,sum(金额) as 总金额 from 货物详况 group by 日期 order by sum(金额)"
rs_sum.CursorLocation = adUseClient
rs_sum.Open sql, conn, adOpenKeyset, adLockPessimistic
addup = 0
MSFlexGrid1.Rows = rs_sum.RecordCount + 2
MSFlexGrid1.Cols = 3
MSFlexGrid1.Row = 0
MSFlexGrid1.Col = 0
MSFlexGrid1.Text = "日期"
MSFlexGrid1.Col = 1
MSFlexGrid1.Text = "总金额"
MSFlexGrid1.Col = 2
MSFlexGrid1.Text = "入出库"
If rs_sum.EOF = False Then
rs_sum.MoveFirst
Do While Not rs_sum.EOF
MSFlexGrid1.Row = MSFlexGrid1.Row + 1
MSFlexGrid1.Col = 0
MSFlexGrid1.Text = rs_sum.Fields(0)
MSFlexGrid1.Col = 1
If CDbl(rs_sum.Fields(1)) < 0 Then
MSFlexGrid1.Text = Replace(rs_sum.Fields(1), "-", "")
MSFlexGrid1.Col = 2
MSFlexGrid1.Text = "出库"
Else
MSFlexGrid1.Text = rs_sum.Fields(1)
MSFlexGrid1.Col = 2
MSFlexGrid1.Text = "入库"
End If
addup = addup + CDbl(rs_sum.Fields(1))
rs_sum.MoveNext
Loop
MSFlexGrid1.Row = MSFlexGrid1.Row + 1
MSFlexGrid1.Col = 0
MSFlexGrid1.Text = "(总计)"
MSFlexGrid1.Col = 1
MSFlexGrid1.Text = addup
End If
rs_sum.Close
End Select
End Sub