求助

朔方

Public Sub 客户往来余额表1()
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim SQL As String, mydata As String, mytable As String
    mydata = "UFDATA_" & [b1] & "_" & [d1] & ""           '指定要修改的数据库
    mytable = "Customer"              '指定数据表
    '清除工作表的所有数据

    '建立与指定SQL Server数据库的连接
    cnn.ConnectionString = "Provider=SQLOLEDB;" _
        & "User ID=sa;" _
        & "Password = " & [i1] & ";" _
        & "Data Source=" & [k1] & ";" _
        & "Initial Catalog =" & mydata
    cnn.Open
    '查询全表某些字段的记录
    sql1 = "select  ccode,ccus_id,sum(md)-sum(mc) as 金额 from " _
    & "GL_accvouch    where  iperiod in (1,2,3,4,5,6,7,8,9,10,11,12) and ccus_id  <>  ''  and  ccode not in (1001,1002)  and  iflag is null group by  ccode,ccus_id  "

  sql2 = "select  ccode,ccus_id,sum(md)-sum(mc)  as 金额 from " _
    & "GL_accvouch    where  iperiod in (1,2,3,4,5,6,7,8,9,10,11,12) and ccus_id  <>  ''  and  ccode not in (1001,1002)  and  iflag is null group by  ccode,ccus_id  "

SQL = "select  *  from ( " & sql1 & "  UNION all " & sql2 & ") "


MsgBox SQL


    Set rs = cnn.Execute(SQL)
    '复制字段名
   For i = 0 To rs.Fields.Count - 1
        Cells(Selection.Row, Selection.Column + i) = rs.Fields(i).Name
        Cells(Selection.Row, Selection.Column + i).Font.Bold = True
    Next i
    '复制全部记录数据
    Cells(Selection.Row + 1, Selection.Column).CopyFromRecordset rs
    Columns.AutoFit
    '关机记录集以及与数据库的连接
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing

End Sub


SQL = "select  *  from ( " & sql1 & "  UNION all " & sql2 & ") "  这一句，请高手看看是那的问题，总是不行

zhaogang1960

可单独测试sql1 和sql2，如果没有问题：
SQL = "sql1 & " UNION all " & sql2 就可以了