是想把按不同方式统计的数据 显示在DataGrid1 中 关键是不同的数据存在不同的表里面
Private Sub DataRefresh()
Dim StrSearch As String
Dim StrGroup As String
Dim TmpSource As String
Dim TmpS1 As String
StrSearch = ""
'判断日期是否正确
If Not IsDate(Trim(txtSDate)) Or Not IsDate(Trim(txtEDate)) Then
MsgBox "请输入正确的日期"
Exit Sub
End If
'读取时间范围
If Len(Trim(txtSDate)) = 0 Then
If Len(Trim(txtEDate)) > 0 Then
StrSearch = " And Ddate<=#" + Trim(txtEDate) + "#"
End If
Else
StrSearch = " And Ddate>=#" + Trim(txtSDate) + "#"
If Len(Trim(txtEDate)) > 0 Then
StrSearch = StrSearch + " And Ddate<=#" + Trim(txtEDate) + "#"
End If
End If
'按统计方式设置查询语句和Group By语句
If cob_By.ListIndex = 0 Then '按销 售 员分类统计
TmpS1 = "Select d.Dsalseman AS 销售员,c.Cname AS 商品名称,c.Cnumber AS 商品货号," _
+ "IIF(SUM(Lamount) IS NULL,0,SUM(Lamount)) AS 售出总数量" _
+ " From Cargo c Left Join"
TmpSource = TmpS1 + "(Select * From Draw Where 1=1 " + StrSearch _
+ " ) v On c.CnumberId=v.CnameId Group By c.Cname,c.Cnumber"
ElseIf cob_By.ListIndex = 1 Then ' 按商品名称分类
TmpS1 = "Select s.t1Name AS 商品名称,s.Cname AS 商品规格,s.Cnumber AS 商品货号," _
+ "IIF(SUM(v.OAmount) IS NULL,0,SUM(v.OAmount)) AS 售出总数量" _
+ " From v_Store s Left Join"
TmpSource = TmpS1 + " (Select * From v_Draw Where 1=1 " + StrSearch _
+ ") v On s.OId=v.OId Group By s.t1Name,s.t2Name,s.OName"
Else: cob_By.ListIndex = 2 ' 按供客户统计
TmpSource = "Select g.Gname AS 客户名称,v.Cname AS 商品名称,v.Cnumber AS 商品货号," _
+ "IIF(SUM(v.Lamount) IS NULL,0,SUM(v.Lamount)) AS 售出总数量" _
+ " From Gust g Left Join v_Draw v On g.GId=v.GId " _
+ " WHERE 1=1 " + StrSearch + " Group By g.Gname,v.Cname,v.Ctype"
End If
Adodc1.ConnectionString = Conn
Adodc1.RecordSource = TmpSource
' MsgBox TmpSource
Adodc1.Refresh
Set DataGrid1.DataSource = Adodc1
DataGrid1.Columns(0).Width = 1600
DataGrid1.Columns(1).Width = 1200
End SubPrivate Sub Cmd_Sts_Click()
DataRefresh
End SubPrivate Sub Form_Load()
cob_By.AddItem "按销 售 员分类", 0
cob_By.AddItem "按商品名称分类", 1
cob_By.AddItem "按 客 户 分 类", 2 cob_By.ListIndex = 2
txtSDate = Date
txtEDate = Date
End Sub
Private Sub DataRefresh()
Dim StrSearch As String
Dim StrGroup As String
Dim TmpSource As String
Dim TmpS1 As String
StrSearch = ""
'判断日期是否正确
If Not IsDate(Trim(txtSDate)) Or Not IsDate(Trim(txtEDate)) Then
MsgBox "请输入正确的日期"
Exit Sub
End If
'读取时间范围
If Len(Trim(txtSDate)) = 0 Then
If Len(Trim(txtEDate)) > 0 Then
StrSearch = " And Ddate<=#" + Trim(txtEDate) + "#"
End If
Else
StrSearch = " And Ddate>=#" + Trim(txtSDate) + "#"
If Len(Trim(txtEDate)) > 0 Then
StrSearch = StrSearch + " And Ddate<=#" + Trim(txtEDate) + "#"
End If
End If
'按统计方式设置查询语句和Group By语句
If cob_By.ListIndex = 0 Then '按销 售 员分类统计
TmpS1 = "Select d.Dsalseman AS 销售员,c.Cname AS 商品名称,c.Cnumber AS 商品货号," _
+ "IIF(SUM(Lamount) IS NULL,0,SUM(Lamount)) AS 售出总数量" _
+ " From Cargo c Left Join"
TmpSource = TmpS1 + "(Select * From Draw Where 1=1 " + StrSearch _
+ " ) v On c.CnumberId=v.CnameId Group By c.Cname,c.Cnumber"
ElseIf cob_By.ListIndex = 1 Then ' 按商品名称分类
TmpS1 = "Select s.t1Name AS 商品名称,s.Cname AS 商品规格,s.Cnumber AS 商品货号," _
+ "IIF(SUM(v.OAmount) IS NULL,0,SUM(v.OAmount)) AS 售出总数量" _
+ " From v_Store s Left Join"
TmpSource = TmpS1 + " (Select * From v_Draw Where 1=1 " + StrSearch _
+ ") v On s.OId=v.OId Group By s.t1Name,s.t2Name,s.OName"
Else: cob_By.ListIndex = 2 ' 按供客户统计
TmpSource = "Select g.Gname AS 客户名称,v.Cname AS 商品名称,v.Cnumber AS 商品货号," _
+ "IIF(SUM(v.Lamount) IS NULL,0,SUM(v.Lamount)) AS 售出总数量" _
+ " From Gust g Left Join v_Draw v On g.GId=v.GId " _
+ " WHERE 1=1 " + StrSearch + " Group By g.Gname,v.Cname,v.Ctype"
End If
Adodc1.ConnectionString = Conn
Adodc1.RecordSource = TmpSource
' MsgBox TmpSource
Adodc1.Refresh
Set DataGrid1.DataSource = Adodc1
DataGrid1.Columns(0).Width = 1600
DataGrid1.Columns(1).Width = 1200
End SubPrivate Sub Cmd_Sts_Click()
DataRefresh
End SubPrivate Sub Form_Load()
cob_By.AddItem "按销 售 员分类", 0
cob_By.AddItem "按商品名称分类", 1
cob_By.AddItem "按 客 户 分 类", 2 cob_By.ListIndex = 2
txtSDate = Date
txtEDate = Date
End Sub
If cob_By.ListIndex = 0 Then '按销 售 员分类统计
TmpS1 = "Select d.Dsalseman AS 销售员,c.Cname AS 商品名称,c.Cnumber AS 商品货号," _
+ "IIF(SUM(Lamount) IS NULL,0,SUM(Lamount)) AS 售出总数量" _
+ " From Cargo c Left Join"
TmpSource = TmpS1 + "(Select * From Draw Where 1=1 " + StrSearch _
+ " ) v On c.CnumberId=v.CnameId Group By c.Cname,c.Cnumber"
ElseIf cob_By.ListIndex = 1 Then ' 按商品名称分类
TmpS2 = "Select s.t1Name AS 商品名称,s.Cname AS 商品规格,s.Cnumber AS 商品货号," _
+ "IIF(SUM(v.OAmount) IS NULL,0,SUM(v.OAmount)) AS 售出总数量" _
+ " From v_Store s Left Join"
TmpSource = TmpS2 + " (Select * From v_Draw Where 1=1 " + StrSearch _
+ ") v On s.OId=v.OId Group By s.t1Name,s.t2Name,s.OName"
Else: cob_By.ListIndex = 2 ' 按供客户统计
TmpS3 = "Select g.Gname AS 客户名称,v.Cname AS 商品名称,v.Cnumber AS 商品货号," _
+ "IIF(SUM(v.Lamount) IS NULL,0,SUM(v.Lamount)) AS 售出总数量" _
+ " From Gust g Left Join v_Draw v On g.GId=v.GId " _
+ " WHERE 1=1 " + StrSearch + " Group By g.Gname,v.Cname,v.Ctype"
End If
TmpSource=TmpS1 & " UNION ALL (SELECT 商品名称,商品规格,商品货号) " & TmpS2 _
& " UNION ALL (SELECT 客户名称,商品名称,商品货号) " & TmpS3' 未知这几个字段类型都是字符型?
Adodc1.ConnectionString = Conn
Adodc1.RecordSource = TmpSource