时间戳

首页 > 解决方案 > 为什么我在 vba 代码中使用 SQL 会收到错误消息？

sql - 为什么我在 vba 代码中使用 SQL 会收到错误消息？

问题描述

你能帮我让这段代码运行吗？我收到一个错误

令牌（无效

con.Open strCon
Set rs = con.Execute("SELECT concat(q.CHSeries, q.CHNumber) as Chassino,q.Model," & _
"Case q.BRANCH when '6' then 'branch 6' when '2' then 'branch 2'  Else 'XX' end Branch," & _
"q.ORDER,q.indt  DeofInvoice,  q.invn Invoice_no, q.customer Customer," & _
"(select coalesce(sum(amnt*ICVL),0) from lbr2 where pcno<>0 and q.order=orno) + (select coalesce(sum(amnt*ICVL),0) from parts2 where pcno<>0 and q.order=orno)+" & _
"(select coalesce(sum(amnt*ICVL),0) from lbr1 where pcno<>0 and q.order=orno) + (select coalesce(sum(amnt*ICVL),0) from parts1 where pcno<>0 and q.order=orno)" & _
"Internal_Sales," & _
"coalesce(case q.InvoiceCredit when 'F' then  (q.RetailwithV-q.V) else -1*(q.RetailwithV-q.V)end,0)  External_Sales" & _
"FROM (select VIN2, VIN3, PRDT, GNN2, orno,  indt, invn, ca30, itot, vx,cd2" & _
"FROM HEADER1 , PARTS1 WHERE BRNN=header1.GNN2 AND ORNO=header1.ORNO union select VIN2, VIN3, PRDT, GNN2, orno,  indt, invn, ca30,  itot, vx,cd2" & _
"FROM HEADER1, LBR1 WHERE BRNN=header1.GNN2 AND ORNO=header1.ORNO" & _
"Union select VIN2, VIN3, PRDT, GNN2, orno, indt, invn, ca30, itot, vx,cd2" & _
"FROM HEADER2 , PARTS2 WHERE BRNN=header2.GNN2 AND ORNO=header2.ORNO union select VIN2, VIN3, PRDT, GNN2, orno, indt, invn, ca30,  itot, vx, cd2" & _
"FROM HEADER2 , LBR2 WHERE BRNN=header2.GNN2 AND ORNO=header2.ORNO)  & q (CHSeries, CHNumber, Model, BRANCH, ORDER,  indt, invn, customer, RetailwithV, V, InvoiceCredit)" & _
"where indt ='2019-09-30'  order by branch")
    For iCols = 0 To rs.Fields.Count - 1
        Worksheets("Sheet1").Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
    Next```

标签： sqlvba

解决方案

由于您没有在 sql 中添加换行符，因此您至少应该在 openig 之后或 close 之前添加空格"。
您发布的 sql 连接到一些乱七八糟的东西。为了便于阅读，我建议：

Dim sql as String
sql = sql & vbCRLF & "SELECT concat(q.CHSeries, q.CHNumber) as Chassino,q.Model, "
sql = sql & vbCRLF & "Case q.BRANCH when '6' then 'branch 6' when '2' then 'branch 2'  Else 'XX' end Branch, "
sql = sql & vbCRLF & "q.ORDER,q.indt  DeofInvoice,  q.invn Invoice_no, q.customer Customer, "
sql = sql & vbCRLF & "(select coalesce(sum(amnt*ICVL),0) from lbr2 where pcno<>0 and q.order=orno) + (select coalesce(sum(amnt*ICVL),0) from parts2 where pcno<>0 and q.order=orno)+ "
sql = sql & vbCRLF & "(select coalesce(sum(amnt*ICVL),0) from lbr1 where pcno<>0 and q.order=orno) + (select coalesce(sum(amnt*ICVL),0) from parts1 where pcno<>0 and q.order=orno) "
sql = sql & vbCRLF & "Internal_Sales, "
sql = sql & vbCRLF & "coalesce(case q.InvoiceCredit when 'F' then  (q.RetailwithV-q.V) else -1*(q.RetailwithV-q.V)end,0)  External_Sales "
sql = sql & vbCRLF & "FROM (select VIN2, VIN3, PRDT, GNN2, orno,  indt, invn, ca30, itot, vx,cd2 "
sql = sql & vbCRLF & "FROM HEADER1 , PARTS1 WHERE BRNN=header1.GNN2 AND ORNO=header1.ORNO union select VIN2, VIN3, PRDT, GNN2, orno,  indt, invn, ca30,  itot, vx,cd2 "
sql = sql & vbCRLF & "FROM HEADER1, LBR1 WHERE BRNN=header1.GNN2 AND ORNO=header1.ORNO "
sql = sql & vbCRLF & "Union select VIN2, VIN3, PRDT, GNN2, orno, indt, invn, ca30, itot, vx,cd2 "
sql = sql & vbCRLF & "FROM HEADER2 , PARTS2 WHERE BRNN=header2.GNN2 AND ORNO=header2.ORNO union select VIN2, VIN3, PRDT, GNN2, orno, indt, invn, ca30,  itot, vx, cd2 "
sql = sql & vbCRLF & "FROM HEADER2 , LBR2 WHERE BRNN=header2.GNN2 AND ORNO=header2.ORNO) "
sql = sql & vbCRLF & "where indt ='2019-09-30'  order by branch "

    Set rs = con.Execute(sql)

推荐阅读