谢谢,这个表连接的时候还出现了一个问题
查询出的xmdd 数据作为e 表,跟a 表连接可取出相关字段,直接用sql是可以查出数据的
但是放到函数中报错:统计_Table4 中没有 付款认定金额列 ,连接的数据源没有问题
如图:
s = "SELECT a.rq AS 订单日期, a.cghthm AS 订单号, a.gysddhm as 单号,a.quyu as 区域,a.zrbm as 办事处,a.cpx as 产品线,a.ddlx AS 订单类型, a.cplx AS 产品类型,a.cglx as 采购类型,"
s = s & " a.gys AS 供应商, a.xmmc AS 项目名称, a.htje AS 订单总金额, ISNULL(b.dccj, 0) AS 抵充差价,isnull(d.退货款,0)as 退货款,isnull(d.非退货款,0)as 非退货款, a.htje + ISNULL(b.dccj, 0) AS 实际应付金额,c.fkrddate as 付款认定日期,c.fkrdje as 付款认定金额,c.sjfkdate as 付款日期,c.fkje as 付款金额,e.jhje as 进货金额,e.ghje as 供货金额,e.bl as 进供货比例,e.yjgs as 业绩归属 FROM cght a "
s = s & " LEFT OUTER JOIN (SELECT cgid, SUM(syje) AS dccj FrOM cgswbcmx GROUP BY cgid) b ON a.id = b.cgid "
s = s & " LEFT OUTER JOIN (SELECT cgid, sjfkdate,fkje, fkrddate,fkrdje FROM cgfk where fkqr=1 ) c ON a.id = c.cgid "
s = s & "LEFT OUTER JOIN (select cgid,sum(退货款) as 退货款,sum(非退款)as 非退货款 from (select cgid,(case when lx = '退货' or lx = '退货 款'then syje else 0 end) as 退货款, (case when lx = '退货' then 0 else syje end) as 非退款 from cgswbcmx) as th group by cgid) as d on a.id = d.cgid"
s = s & "LEFT OUTER JOIN(select xmdhout,jhje,ghje,yjgs ,(case when ghje >0 then(jhje/ghje) else 0 end ) as bl from xmdd) as e on a.gysddhm = e.xmdhout where a.gysddhm<>'' and e.xmdhout<>''" '与xmdd做表连接,取出进货金额,供货金额,进供货比例,业绩归属等字段
s=s & tj & fw & " order by c.fkrddate " '条件\范围
msgbox("执行")------------------------->这里可以执行 ,然后就报错了,也就是s 有问题,但是我用sql查询,是没有问题的
Tables("统计_Table4").Fill( s , "zt",True)
Tables("统计_Table4").SetColVisibleWidth("付款认定日期|90|供应商|160|订单类型|70|产品线|60|产品类型|70|采购订单号|110|付款认定金额|100|订单总金额|100|区域|50|办事处|70|项目名称|160")
DataTables("统计_Table4").DataCols("付款认定金额").SetFormat("#,##0.00")
DataTables("统计_Table4").DataCols("订单总金额").SetFormat("#,##0.00")
msgbox("执行1")
此主题相关图片如下:1.png
此主题相关图片如下:2.png
此主题相关图片如下:3.png