查询表：订单主表的合同状态和订单开piao的{订单开piao}.客户名称", "{订单开piao}.合同编号", "{订单开piao}.产品编号","{订单开piao}.产品名称","{订单开piao}.规格型号","{订单主表}.合同状态","{订单开piao}.日期")，组成一个查询表。
弹出：语法错误或违反访问规则，麻烦修改一下。
Tables("查询订单_Table1").StopRedraw
Dim Filter1 As String
With e.Form.Controls("起始日期")
If .Value IsNot Nothing Then
If Filter1 >"" Then
Filter1 = Filter1 & " And "
End If
Filter1 = Filter1 & "{订单开piao}.日期 < '" & .Value & "'"
End If
End With
Dim jb As New SQLJoinTableBuilder("查询表1","订单开piao")
jb.C
jb.AddTable("订单开piao","合同编号","订单主表","合同编号")
jb.AddCols("{订单开piao}.客户名称", "{订单开piao}.合同编号", "{订单开piao}.产品编号","{订单开piao}.产品名称","{订单开piao}.规格型号","{订单主表}.合同状态","{订单开piao}.日期")
jb.AddExp("fa piao数量", "fa piao数量")
jb.AddExp("fa piao重量", "fa piao重量")
Dim bd3 As new GroupTableBuilder("统计表1",jb.BuildSQL,"jyxx")
Dim dt3 As fxDataSource
bd3.Groups.AddDef("客户名称") '根据型号分组
bd3.Groups.AddDef("合同编号") '根据型号分组
bd3.Groups.AddDef("产品编号") '根据型号分组
bd3.Groups.AddDef("产品名称") '根据型号分组
bd3.Groups.AddDef("规格型号") '根据型号分组
bd3.Groups.AddDef("合同状态") '根据型号分组
bd3.Totals.AddDef("fa piao数量","fa piao数量") '对数量进行统计
bd3.Totals.AddDef("fa piao重量","fa piao重量") '对金额进行统计
bd3.Filter = Filter1
dt3 = bd3.BuildDataSource()
Dim nms As String() = {"客户名称","合同编号","产品编号","产品名称","规格型号","合同状态"} '指定连接列
Tables("查询订单_Table1").DataSource = dt3 '将统计结果绑定到Table
With DataTables("查询订单_Table1").DataCols '用表达式列计算库存数据
.Add("期初开piao数量",Gettype(Double), "IsNull([fa piao数量],0)","期初开piao数量")
.Add("期初开piao重量",Gettype(Double), "IsNull([fa piao重量],0)","期初开piao重量")
End With
Dim Filter As String
With e.Form.Controls("起始日期")
If .Value IsNot Nothing Then
If Filter >"" Then
Filter = Filter & " And "
End If
Filter = Filter & "{订单开piao}.日期 >= '" & .Value & "'"
End If
End With
With e.Form.Controls("结束日期")
If .Value IsNot Nothing Then
If Filter >"" Then
Filter = Filter & " And "
End If
Filter = Filter & "{订单开piao}.日期 <= '" & .Value & "'"
End If
End With
Dim jb1 As New SQLJoinTableBuilder("查询表1","订单开piao")
jb1.C
jb1.AddTable("订单开piao","合同编号","订单主表","合同编号")
jb1.AddCols("{订单开piao}.客户名称","{订单开piao}.合同编号","{订单开piao}.产品编号","{订单开piao}.产品名称","{订单开piao}.规格型号","{订单主表}.合同状态","{订单开piao}.日期")
jb1.AddExp("fa piao数量", "fa piao数量")
jb1.AddExp("fa piao重量", "fa piao重量")
Dim bd1 As new GroupTableBuilder("统计表1",jb1.BuildSQL,"jyxx")
Dim dt1 As fxDataSource
bd1.Groups.AddDef("客户名称") '根据型号分组
bd1.Groups.AddDef("合同编号") '根据型号分组
bd1.Groups.AddDef("产品编号") '根据型号分组
bd1.Groups.AddDef("产品名称") '根据型号分组
bd1.Groups.AddDef("规格型号") '根据型号分组
bd1.Groups.AddDef("合同状态") '根据型号分组
bd1.Totals.AddDef("fa piao数量","fa piao数量") '对数量进行统计
bd1.Totals.AddDef("fa piao重量","fa piao重量") '对金额进行统计
bd1.Filter = Filter
dt1 = bd1.BuildDataSource()
dt1.Combine(nms,dt3,nms) '将退货统计数据组合到进货统计数据
Tables("查询订单_Table1").DataSource = dt1 '将统计结果绑定到Table
With DataTables("查询订单_Table1").DataCols '用表达式列计算库存数据
.Add("期末开piao数量",Gettype(Double), "IsNull([期初开piao数量],0) + IsNull([fa piao数量],0)","期末开piao数量")
.Add("期末开piao重量",Gettype(Double), "IsNull([期初开piao重量],0) + IsNull([fa piao重量],0)","期末开piao重量")
End With
Tables("查询订单_Table1").SetColVisibleWidth("客户名称|80|合同编号|70|产品编号|60|产品名称|100|规格型号|80|合同状态|80|期初开piao数量|90|期初开piao重量|90|fa piao数量|90|fa piao重量|90|期末开piao数量|90|期末开piao重量|90")
Tables("查询订单_Table1").DefaultRowHeight = 35
DataTables("查询订单_Table1").DataCols("期初开piao数量").SetFormat("#,###.00")
DataTables("查询订单_Table1").DataCols("期初开piao重量").SetFormat("#,###.00")
DataTables("查询订单_Table1").DataCols("fa piao数量").SetFormat("#,###.00")
DataTables("查询订单_Table1").DataCols("fa piao重量").SetFormat("#,###.00")
DataTables("查询订单_Table1").DataCols("期末开piao数量").SetFormat("#,###.00")
DataTables("查询订单_Table1").DataCols("期末开piao重量").SetFormat("#,###.00")
Tables("查询订单_Table1").Sort = "客户名称"
'Tables("查询订单_Table1").Cols().TextAlign = TextAlignEnum.Center
'Tables("查询订单_Table1").Cols("月").TextAlign = TextAlignEnum.Center
Tables("查询订单_Table1").Cols("产品编号").TextAlign = TextAlignEnum.Center
With Tables("查询订单_Table1")
.MergeMode = MergeModeEnum.Standard '标准合并模式
.MergeCols.Clear() '清除原来的合并列
.MergeCols.Add("合同编号")
.MergeCols.Add("客户名称") '加入要合并的列
.MergeSort = "客户名称,合同编号" '设置合并模式下的排序方式
.AllowMerge = True '启用合并模式
.Cols("期初开piao数量").GrandTotal = True
.Cols("期初开piao重量").GrandTotal = True
.Cols("fa piao数量").GrandTotal = True
.Cols("fa piao重量").GrandTotal = True
.Cols("期末开piao数量").GrandTotal = True
.Cols("期末开piao重量").GrandTotal = True
.GrandTotal = True
End With
Tables("查询订单_Table1").AutoSizeCols()
DataTables("查询订单_Table1").RemoveFor("([期初开piao数量] =0 or [期初开piao数量] is null) and ([期初开piao重量] =0 or [期初开piao重量] is null) and [fa piao数量] is null and [fa piao重量] is null and [期末开piao数量] =0 and [期末开piao重量] =0")
DataTables("查询订单_Table1").RemoveFor("合同状态 ='已完成'")
Tables("查询订单_Table1").ResumeRedraw
加好友 
发短信
Post By：2015/11/17 15:40:00 [只看该作者]
