CODEWITHSUNDEEP
sqlvbaexcel
mapaul1977
mapaul1977

Reputation: 21

Expr1000 error when performing a SUM on a SQL query in Excel

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

Thank you for your help! - Zokah

Upvotes: 1

Views: 2431

Answers (1)

Y.B.
Y.B.

Reputation: 3586

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';"

Upvotes: 1

Related Questions