在Excel中的SQL查询上执行SUM时的Expr1000错误 [英] Expr1000 error when performing a SUM on a SQL query in Excel

问题描述

以下查询在excel中提供结果:"Expr1000 RESULT"我需要获取不带标题的结果，但我发现什么都无法完成.我在查询中找不到任何冗余，但是当我删除SUM函数时，标题恢复为该列的正确标题.我非常感谢任何想法.

The following query is providing the result in excel: "Expr1000 RESULT" I need to get the result without a header and nothing I've found will accomplish this. I can't find any redundancies in the query, but when I remove the SUM function, the header reverts back to the column's correct header. I very much appreciate any ideas.

Sub CalculateComplete()
Sheet2.Cells.ClearContents

Dim oCn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim ConnString As String
Dim SQL As String

Dim QueryDPiN As String
QueryDPiN = "SELECT IIF(SUM([Amount Due]) IS NULL, 0, SUM([Amount Due])) FROM [OP$] WHERE [Originator Dept]='BLC New Business' AND [Overpayment Category]='Dollars Paid in Error';"

Call SQLQueries(QueryDPiN, "C2")

End Sub

Sub SQLQueries(QueryVariable As String, TableLocation As String)

Dim oCn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim ConnString As String
Dim SQL As String
Dim qt As QueryTable

ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\cnop0g\Desktop\OP.xlsx;Extended Properties=Excel 8.0;Persist Security Info=False"
Set oCn = New ADODB.Connection
oCn.ConnectionString = ConnString
oCn.ConnectionTimeout = 30
oCn.Open

SQL = QueryVariable

Set oRS = New ADODB.Recordset
oRS.Source = SQL
oRS.ActiveConnection = oCn
oRS.Open

Set qt = Worksheets(6).QueryTables.Add(Connection:=oRS, _
Destination:=Worksheets(6).Range(TableLocation))

    qt.Refresh

If oRS.State <> adStateClosed Then
oRS.Close
End If

If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCn Is Nothing Then Set oCn = Nothing

End Sub

谢谢您的帮助！-Zokah

Thank you for your help! - Zokah

推荐答案

您需要在查询中指定列名称 AS [到期金额] ，以使SQL引擎不会自动生成一个名称:

You need to specify column name AS [Amount Due] in your query so that SQL engine would not generate one automatically:

QueryDPiN = "SELECT IIF(SUM([Amount Due]) IS NULL, 0, SUM([Amount Due])) AS [Amount Due] FROM [OP$] WHERE [Originator Dept]='BLC New Business' AND [Overpayment Category]='Dollars Paid in Error';"

这篇关于在Excel中的SQL查询上执行SUM时的Expr1000错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！