数据库表求和查询代码运行出错

ybc76

Sub HZsj()
  Dim Cnn As New ADODB.Connection
  Dim Rst As New ADODB.Recordset
  Dim pthStr, SQL As String, mytable As String
  Dim Jcount As Integer, a, b As Date, c As String, d As String
  mytable = "xbshuju"
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  spassword = 66881
ActiveSheet.Unprotect spassword
   Sheet1.Range("A4:O1000000") = ""

    '建立与指定SQL Server数据库的连接
Cnn.ConnectionString = "Provider=SQLOLEDB;" _
        & "User ID=rxgl;" _
        & "Password =ybc.085576;" _
        & "Data Source='" & Sheets("sz").Cells(2, 2) & "';" _
        & "Initial Catalog ='" & Sheets("sz").Cells(4, 2) & "'"
    Cnn.Open
    a = Sheet1.Range("b2")
    b = Sheet1.Range("C2")
    d = Sheet1.Range("h1")
  '查询全表某些字段记录

  SQL = "select 工序,姓名,产品大类,产品中类,产品小类,产品名称,sum(iif(isnull(领料数),0,领料数)) ,sum(iif(isnull(合格数),0,合格数)) ,sum(iif(isnull(实际领料),0,实际领料)) ,sum(iif(isnull(实际合格),0,实际合格)) from " & mytable & " where 日期 >= '" & a & "' and 日期 <='" & b & "'  order by 日期"
  Set Rst = Cnn.Execute(SQL)

'复制字段记录
    Sheet1.Range("a4").CopyFromRecordset Rst
      Rst.Close
      Cnn.Close
      Set Rst = Nothing
      Set Cnn = Nothing
   a = [a:a].Find("*", , xlValues, , , xlPrevious).Row
    With [a1000000].End(xlUp)
        .Offset(3, 0) = "产量合计"
        .Offset(3, 4).Formula = "=SUBTOTAL(109,E3:E" & Sheet11.Range("E3", "E" & a).Count + 2 & ")"
        .Offset(3, 5).Formula = "=SUBTOTAL(109,F3:F" & Sheet11.Range("F3", "F" & a).Count + 2 & ")"
        .Offset(3, 6).Formula = "=SUBTOTAL(109,G3:G" & Sheet11.Range("G3", "G" & a).Count + 2 & ")"
    End With

    Sheet1.Range("A3:O3").Select
    Selection.AutoFilter
Cells(3, 1).Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

amazeyeli

1.掉了group by
2.order by 并不存在指定的字段:日期
3.>= and <= 请用 between

ybc76

好像是提示isnull附近语法错误

ybc76

提示isnull函数要求有两个参数