筛选后如何两条件引用文本内容

saoxu

我对符合要求的销售数据进行筛选出需要销售情况，但现在还需要引入对应的促销标准，发上附件，麻烦大家帮忙

saoxu

顶自己一下，其实这是上一题后发现的第二个问题，想问能不能添加  LEFT JION这样来添加呢，试了好几次都不行，

saoxu

Sub 统计()
    Dim cnn As Object, SQL$
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "Provider = Microsoft.ace.Oledb.12.0;Extended Properties =Excel 12.0;Data Source =" & ThisWorkbook.FullName
    Sheet56.Range("A2:D9999").ClearContents
For i = 2 To Range("G9999").End(xlUp).Row
    SQL = "Select 往来单位银行户名,产地,商品编号,Sum(包数),赠送规则 From [销售明细表$A1:M" & [销售明细表!A65536].End(xlUp).Row & "] " _
     & " left join [送包表$G1:I" & [送包表!I65536].End(xlUp).Row & "] on [销售明细表$].往来单位银行户名=[送包表$G1:I" & [送包表!I65536].End(xlUp).Row & "].客户 and [销售明细表$].产地=[送包表$G1:I" & [送包表!I65536].End(xlUp).Row & "].产地" _
     & " Where 往来单位银行户名 = '" & Sheets("送包表").Cells(i, 7) & "' and 产地 like REPLACE('" & Sheets("送包表").Cells(i, 8) & "','全部','%') Group By 往来单位银行户名,产地,商品编号"
    Sheet56.Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 1).CopyFromRecordset cnn.Execute(SQL)
Next
    cnn.Close
Set cnn = Nothing
End Sub

saoxu

显示 left join 语法错误，麻烦帮我看出哪里错了

saoxu

Sub 统计()
    Dim cnn As Object, SQL$
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "Provider = Microsoft.ace.Oledb.12.0;Extended Properties =Excel 12.0;Data Source =" & ThisWorkbook.FullName
    Sheet56.Range("A2:D9999").ClearContents
For i = 2 To Range("G9999").End(xlUp).Row
    SQL = "Select [销售明细表$].往来单位银行户名,[销售明细表$].产地,[销售明细表$].商品编号,Sum([销售明细表$].包数),[送包表$].赠送规则 From [销售明细表$A1:M" & [销售明细表!A65536].End(xlUp).Row & "] " _
     & " left join [送包表$G1:I" & [送包表!I65536].End(xlUp).Row & "] on [送包表$A1:E" & [送包表!E65536].End(xlUp).Row & "].往来单位银行户名=[送包表$G1:I" & [送包表!I65536].End(xlUp).Row & "].客户 and [送包表$A1:E" & [送包表!E65536].End(xlUp).Row & "].产地=[送包表$G1:I" & [送包表!I65536].End(xlUp).Row & "].产地" _
     & " Where [销售明细表$].往来单位银行户名 = '" & Sheets("送包表").Cells(i, 7) & "' and [销售明细表$].产地 like REPLACE('" & Sheets("送包表").Cells(i, 8) & "','全部','%') Group By [销售明细表$].往来单位银行户名,[销售明细表$].产地,[销售明细表$].商品编号"
    Sheet56.Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 1).CopyFromRecordset cnn.Execute(SQL)
Next
    cnn.Close
Set cnn = Nothing
End Sub

saoxu

都标上表名了，依然显示JOIN 语法错误

saoxu

试了一下午还是不行，请问是不是字段哪里写错了写法

saoxu

好像是加了表名吗，还是没有老师来看一看。再顶

saoxu

顶一下呀，我也不懂SQL，就是复制过来后修改的，真不知哪里错了

saoxu

老是显示JOIN操作语法错误