SQL Query in Excel VBA - Runtime Error 1004
I only receive the error with the "SET" Command, the code producing the error is:
SET @mydate = CONVERT(VARCHAR(25),GETDATE(),101)
SET @First = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101)
SET @Last = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101)
The Query runs perfectly in Management Studio and if I remove the SET commands I no longer receive the error. :banghead:
The Entire Macro:
GetAgencySubmissions()
Dim SQL
SQL = "DECLARE @mydate DATETIME SET @mydate = CONVERT(VARCHAR(25),GETDATE(),101)" + vbNewLine
SQL = SQL + "DECLARE @First AS DATETIME SET @First = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) " + vbNewLine
SQL = SQL + "DECLARE @Last AS DATETIME SET @Last = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101)" + vbNewLine
SQL = SQL + "" + vbNewLine
SQL = SQL + "SELECT 'MW & W' = (Select COUNT(Offices.PA_State) " + vbNewLine
SQL = SQL + "FROM [DataBase].dbo.Offices WHERE Offices.PA_EnterpriseID <> '1'AND" + vbNewLine
SQL = SQL + "Offices.PA_State IN ('AR', 'IA', 'KS', 'NM', 'MO', 'NE', 'OK', 'SD', 'TX', 'CO', 'HI', 'MN', 'NV') " + vbNewLine
SQL = SQL + "AND ISNULL(CONVERT(VARCHAR,Offices.PA_DateSubmitted,101), '') BETWEEN @First AND @Last), " + vbNewLine
SQL = SQL + "" + vbNewLine
SQL = SQL + "'West' = (Select COUNT(Offices.PA_State) " + vbNewLine
SQL = SQL + "FROM [DataBase].dbo.Offices WHERE Offices.PA_EnterpriseID <> '1' AND " + vbNewLine
SQL = SQL + "Offices.PA_State IN ('CO', 'HI', 'NM', 'NV') " + vbNewLine
SQL = SQL + "AND ISNULL(CONVERT(VARCHAR,Offices.PA_DateSubmitted,101), '') BETWEEN @First AND @Last), " + vbNewLine
SQL = SQL + "" + vbNewLine
SQL = SQL + "'Mid-West' = (Select COUNT(Offices.PA_State) " + vbNewLine
SQL = SQL + "FROM [DataBase].dbo.Offices WHERE Offices.PA_EnterpriseID <> '1' AND " + vbNewLine
SQL = SQL + "Offices.PA_State IN ('AR', 'IA', 'KS', 'MN', 'MO', 'NE', 'OK', 'SD', 'TX') " + vbNewLine
SQL = SQL + "AND ISNULL(CONVERT(VARCHAR,Offices.PA_DateSubmitted,101), '') BETWEEN @First AND @Last), " + vbNewLine
SQL = SQL + "" + vbNewLine
SQL = SQL + "'East' = (Select COUNT(Offices.PA_State) " + vbNewLine
SQL = SQL + "FROM [DataBase].dbo.Offices WHERE Offices.PA_EnterpriseID <> '1' AND " + vbNewLine
SQL = SQL + "Offices.PA_State IN ('AL', 'CT', 'DE', 'FL', 'IN', 'MD', 'MI', 'MS', 'NC', 'NH', 'PA', 'RI', 'SC', 'TN', 'VA', 'VT', 'WV') " + vbNewLine
SQL = SQL + "AND ISNULL(CONVERT(VARCHAR,Offices.PA_DateSubmitted,101), '') BETWEEN @First AND @Last), " + vbNewLine
SQL = SQL + "" + vbNewLine
SQL = SQL + "'FCUG' = (Select COUNT(Offices.PA_State) " + vbNewLine
SQL = SQL + "FROM [DataBase].dbo.Offices WHERE Offices.PA_EnterpriseID <> '1' AND " + vbNewLine
SQL = SQL + "Offices.PA_State <> 'CA' AND ISNULL(CONVERT(VARCHAR,Offices.PA_DateSubmitted,101), '') BETWEEN @First AND @Last), " + vbNewLine
SQL = SQL + "" + vbNewLine
SQL = SQL + "'FIA' = (Select COUNT(Offices.PA_State) " + vbNewLine
SQL = SQL + "FROM [DataBase].dbo.Offices WHERE Offices.PA_EnterpriseID <> '1' AND " + vbNewLine
SQL = SQL + "Offices.PA_State = 'CA' AND ISNULL(CONVERT(VARCHAR,Offices.PA_DateSubmitted,101), '') BETWEEN @First AND @Last)," + vbNewLine
SQL = SQL + "" + vbNewLine
SQL = SQL + "COUNT(Offices.PA_State) AS 'All'" + vbNewLine
SQL = SQL + "FROM [DataBase].dbo.Offices " + vbNewLine
SQL = SQL + "WHERE Offices.PA_EnterpriseID <> '1'AND ISNULL(CONVERT(VARCHAR,Offices.PA_DateSubmitted,101), '') BETWEEN @First AND @Last" + vbNewLine
With ThisWorkbook.Sheets("Agency Data").QueryTables.Add( _
Connection:="ODBC;DRIVER=SQL Server;SERVER=****;UID=****;PWD=****;APP=Microsoft Open Database Connectivity;" & _
"DATABASE=****", Destination:=ThisWorkbook.Sheets("Agency Data").Range("I" & X))
.CommandText = SQL
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
ThisWorkbook.Sheets("Agency Data").Cells.QueryTable.Delete
End Sub
