0

I've written a SQL code in access 2013 where it runs smoothly and quickly (i reduced query time by 20 seconds with this code). When I bring it into excel vba to pull to a spreadsheet, i get the error that is in the title of this post.

I made two edits. First any text in a where clause that was surrounded by brackets, I double bracketed. (From "Tempur-Pedic" to ""Tempur-Pedic")

Second, I added Sql = " to the first line and sql = sql & " to all subsequent lines.

I hope it's simple! Any help is appreciated.

Rich

The code is as follows:

Sql = "TRANSFORM Sum(Fin2.Vel) AS Velocity"
Sql = Sql & " SELECT Fin2.IC, Fin2.IB, Fin2.COL, Fin2.[IS]"
Sql = Sql & " FROM (Select Fin.Ic, Fin.IB, Fin.Col,Fin.IS, Fin.Yr & ""_"" & Fin.Wk as Yr_Week, (Fin.tdu/fin.fsu) as Vel"
Sql = Sql & " From (SELECT FS2.[Item Company] as IC, FS2.[Item Brand] as IB, FS2.[Item Collection] as Col, FS2.[Item SubType] as IS, FS2.WeekNumber as Wk, FS2.Year as Yr, FS2.FSu, TD2.Tdu FROM (SELECT FS1.[Item Company], FS1.[Item Brand], FS1.[Item Collection], FS1.[Item SubType], FS1.WeekNumber, FS1.Year, Sum(FS1.SumOfQTY) AS FSu FROM"
Sql = Sql & " (SELECT VSNConversionData.Item_Category AS IC, [Sleepys Store List].DC, VSNConversionData.[Item Company], VSNConversionData.[Item Brand], VSNConversionData.[Item Collection], VSNConversionData.[Item SubType], FloorModels2.WeekNumber, FloorModels2.Year, Sum(FloorModels2.QTY) AS SumOfQTY"
Sql = Sql & " FROM [Sleepys Store List] INNER JOIN (VSNConversionData INNER JOIN FloorModels2 ON VSNConversionData.VSN = FloorModels2.VSN) ON [Sleepys Store List].[Store Code] = FloorModels2.[Source Org]"
Sql = Sql & " Where (((FloorModels2.WeekNumber) Between 42 and 53) and  ((FloorModels2.Year) = 2015))"
Sql = Sql & " GROUP BY VSNConversionData.Item_Category, [Sleepys Store List].DC, VSNConversionData.[Item Company], VSNConversionData.[Item Brand], VSNConversionData.[Item Collection], VSNConversionData.[Item SubType], FloorModels2.WeekNumber, FloorModels2.Year)  AS FS1"
Sql = Sql & " WHERE ((((FS1.IC) = ""Mattress"") And ((FS1.[Item Company]) = ""Tempur-Pedic"")) Or (((FS1.IC) = ""Mattress"") And ((FS1.[Item Company]) = ""Sealy"") And ((FS1.DC) <> ""950"")))"
Sql = Sql & " GROUP BY FS1.[Item Company], FS1.[Item Brand], FS1.[Item Collection], FS1.[Item SubType], FS1.WeekNumber, FS1.Year)  AS FS2"
Sql = Sql & " Left Join"
Sql = Sql & " (SELECT TD1.[Item Company], TD1.[Item Brand], TD1.[Item Collection], TD1.[Item SubType], TD1.WeekNum, TD1.Year, Sum(TD1.SumOfQTY) AS TDu"
Sql = Sql & " FROM (SELECT VSNConversionData.Item_Category AS IC, [Sleepys Store List].DC, VSNConversionData.[Item Company], VSNConversionData.[Item Brand], VSNConversionData.[Item Collection], VSNConversionData.[Item SubType], StoreSalesData.WeekNum, StoreSalesData.Year, Sum(StoreSalesData.QTY) AS SumOfQTY"
Sql = Sql & " FROM [Sleepys Store List] INNER JOIN (VSNConversionData INNER JOIN StoreSalesData ON VSNConversionData.VSN = StoreSalesData.VSN) ON [Sleepys Store List].[Store Code] = StoreSalesData.STR"
Sql = Sql & " WHERE (((StoreSalesData.WeekNum)Between 42 and 53) AND ((StoreSalesData.Year)=2015))"
Sql = Sql & " GROUP BY VSNConversionData.Item_Category, [Sleepys Store List].DC, VSNConversionData.[Item Company], VSNConversionData.[Item Brand], VSNConversionData.[Item Collection], VSNConversionData.[Item SubType], StoreSalesData.WeekNum, StoreSalesData.Year)  AS TD1"
Sql = Sql & " WHERE (((TD1.IC) = ""Mattress"") And ((TD1.[Item Company]) = ""Tempur-pedic"")) Or (((TD1.IC) = ""Mattress"") And ((TD1.[Item Company]) = ""Sealy"") And ((TD1.DC) <> ""950""))"
Sql = Sql & " GROUP BY TD1.[Item Company], TD1.[Item Brand], TD1.[Item Collection], TD1.[Item SubType], TD1.WeekNum, TD1.Year)  AS TD2"
Sql = Sql & " On ((FS2.[Item Subtype]=TD2.[Item Subtype]) AND (FS2.[Weeknumber]=TD2.Weeknum) and (FS2.year = TD2.year))) as Fin"
Sql = Sql & " Union"
Sql = Sql & " Select Fin.Ic, Fin.IB, Fin.Col,Fin.IS, Fin.Yr & ""_"" & Fin.Wk as Yr_Week, (Fin.tdu/fin.fsu) as Vel"
Sql = Sql & " FROM"
Sql = Sql & " (SELECT FS2.[Item Company] as IC, FS2.[Item Brand] as IB, FS2.[Item Collection] as Col, FS2.[Item SubType] as IS, FS2.WeekNumber as Wk, FS2.Year as Yr, FS2.FSu, TD2.Tdu"
Sql = Sql & " FROM"
Sql = Sql & " (SELECT FS1.[Item Company], FS1.[Item Brand], FS1.[Item Collection], FS1.[Item SubType], FS1.WeekNumber, FS1.Year, Sum(FS1.SumOfQTY) AS FSu"
Sql = Sql & " FROM"
Sql = Sql & " (SELECT VSNConversionData.Item_Category AS IC, [Sleepys Store List].DC, VSNConversionData.[Item Company], VSNConversionData.[Item Brand], VSNConversionData.[Item Collection], VSNConversionData.[Item SubType], FloorModels2.WeekNumber, FloorModels2.Year, Sum(FloorModels2.QTY) AS SumOfQTY"
Sql = Sql & " FROM [Sleepys Store List] INNER JOIN (VSNConversionData INNER JOIN FloorModels2 ON VSNConversionData.VSN = FloorModels2.VSN) ON [Sleepys Store List].[Store Code] = FloorModels2.[Source Org]"
Sql = Sql & " Where (((FloorModels2.WeekNumber) Between 1 and 1) and  ((FloorModels2.Year) = 2016))"
Sql = Sql & " GROUP BY VSNConversionData.Item_Category, [Sleepys Store List].DC, VSNConversionData.[Item Company], VSNConversionData.[Item Brand], VSNConversionData.[Item Collection], VSNConversionData.[Item SubType], FloorModels2.WeekNumber, FloorModels2.Year)  AS FS1"
Sql = Sql & " WHERE ((((FS1.IC) = ""Mattress"") And ((FS1.[Item Company]) = ""Tempur-Pedic"")) Or (((FS1.IC) = ""Mattress"") And ((FS1.[Item Company]) = ""Sealy"") And ((FS1.DC) <> ""950"")))"
Sql = Sql & " GROUP BY FS1.[Item Company], FS1.[Item Brand], FS1.[Item Collection], FS1.[Item SubType], FS1.WeekNumber, FS1.Year)  AS FS2"
Sql = Sql & " Left Join"
Sql = Sql & " (SELECT TD1.[Item Company], TD1.[Item Brand], TD1.[Item Collection], TD1.[Item SubType], TD1.WeekNum, TD1.Year, Sum(TD1.SumOfQTY) AS TDu"
Sql = Sql & " FROM (SELECT VSNConversionData.Item_Category AS IC, [Sleepys Store List].DC, VSNConversionData.[Item Company], VSNConversionData.[Item Brand], VSNConversionData.[Item Collection], VSNConversionData.[Item SubType], StoreSalesData.WeekNum, StoreSalesData.Year, Sum(StoreSalesData.QTY) AS SumOfQTY"
Sql = Sql & " FROM [Sleepys Store List] INNER JOIN (VSNConversionData INNER JOIN StoreSalesData ON VSNConversionData.VSN = StoreSalesData.VSN) ON [Sleepys Store List].[Store Code] = StoreSalesData.STR"
Sql = Sql & " WHERE (((StoreSalesData.WeekNum)Between 1 and 1) AND ((StoreSalesData.Year)=2016))"
Sql = Sql & " GROUP BY VSNConversionData.Item_Category, [Sleepys Store List].DC, VSNConversionData.[Item Company], VSNConversionData.[Item Brand], VSNConversionData.[Item Collection], VSNConversionData.[Item SubType], StoreSalesData.WeekNum, StoreSalesData.Year)  AS TD1"
Sql = Sql & " WHERE (((TD1.IC) = ""Mattress"") And ((TD1.[Item Company]) = ""Tempur-pedic"")) Or (((TD1.IC) = ""Mattress"") And ((TD1.[Item Company]) = ""Sealy"") And ((TD1.DC) <> ""950""))"
Sql = Sql & " GROUP BY TD1.[Item Company], TD1.[Item Brand], TD1.[Item Collection], TD1.[Item SubType], TD1.WeekNum, TD1.Year)  AS TD2"
Sql = Sql & " On ((FS2.[Item Subtype]=TD2.[Item Subtype]) AND (FS2.[Weeknumber]=TD2.Weeknum) and (FS2.year = TD2.year))) as Fin)  AS Fin2"
Sql = Sql & " GROUP BY Fin2.IC, Fin2.IB, Fin2.COL, Fin2.[IS]"
Sql = Sql & " PIVOT Fin2.Yr_Week;"
RichWolff
  • 45
  • 8
  • quotes and brackets are not the same thing... – Taylor Brown Jan 14 '16 at 16:24
  • it does not. There are quite a few errors. Seems like there are were a couple extra breaks, which i've experience before but access usually corrects it. I've updated the code in access, had it run successfully again, and pasted it to vba. Had to break the lines out a bit because they were too long to fit and I'm back in the same boat. – RichWolff Jan 14 '16 at 16:51
  • I'm going to need to use variables that change from day to day which are based on a cell in the workbook. I wish it were that easy! – RichWolff Jan 14 '16 at 17:06

1 Answers1

1

I would go through your code and make sure every field is surrounded by brackets... example:

TD1.Year becomes TD1.[Year]

This error is usually fixed by doing this.

Taylor Brown
  • 1,689
  • 2
  • 17
  • 33