Hi,
I found some errors and corrected them which was causing it to fall through
to the last line.  I have realigned it for easier reading and need you to
compare to your tables now for incorrect field/table names.  Watch for line
wrapping after pasting back into the VBA window.

Option Compare Database
Option Explicit

Private Sub ExtractForecastPurchases_Click()
   Dim dbsCurrent As Database
   Dim qryTest As QueryDef
   Dim CreateSQL01 As String
   Dim CreateSQL02 As String
   Dim CreateSQL03 As String
   Dim CreateSQL04 As String
   Dim CreateSQL_All As String

   CreateSQL01 = "SELECT tblHFIMR357PlannPurchasesExtract.[VENDOR], " _
               & "tblHFIMR357PlannPurchasesExtract.[NAME], " _
               & "tblHFIMR357PlannPurchasesExtract.[PART NUMBER], " _
               & "tblHFIMR357PlannPurchasesExtract." _
               & "tblHFIMR357Purchases_Description , """ _
               & "tblHFIMR357PlannPurchasesExtract.AMPR," _
               & "tblHFIMR357PlannPurchasesExtract.[ORD-QTY] , """ _
               & "tblHFIMR357PlannPurchasesExtract.ABC,"
   CreateSQL02 = "tblHFIMR357PlannPurchasesExtract.SteelClass , """ _
               & "tblHFIMR357PlannPurchasesExtract.DFClass," _
               & "tblHFIMR357PlannPurchasesExtract.Currency , """ _
               & "tblHFIMR357PlannPurchasesExtract.BYR, [ComMgrFName] &
"""" & [commgrLName] AS ComName," _
               & "tblHFIMR357PlannPurchasesExtract.[M-GP], " _
               & "tblHFIMR357PlannPurchasesExtract.
tblMaterialGroup_Description," _
               & "tblHFIMR357PlannPurchasesExtract.SMAT, IIf(
[MultipleQte]1,'YES' , '""') AS AVGQte, "
   CreateSQL03 = "tblHFIMR357PlannPurchasesExtract.Period01 AS " & [MO01] &
"Qty, [Period01]*[SMAT] AS " & [MO01] & "Spend, " _
               & "tblHFIMR357PlannPurchasesExtract.Period02 AS " & [Mo02] &
"Qty, [Period02]*[SMAT] AS " & [Mo02] & "Spend, " _
               & "tblHFIMR357PlannPurchasesExtract.Period03 AS " & [Mo03] &
"Qty, [Period03]*[SMAT] AS " & [Mo03] & "Spend, " _
               & "tblHFIMR357PlannPurchasesExtract.Period04 AS " & [Mo04] &
"Qty, [Period04]*[SMAT] AS " & [Mo04] & "Spend, " _
               & "tblHFIMR357PlannPurchasesExtract.Period05 AS " & [Mo05] &
"Qty, [Period05]*[SMAT] AS " & [Mo05] & "Spend, " _
               & "tblHFIMR357PlannPurchasesExtract.Period06 AS " & [Mo06] &
"Qty, [Period06]*[SMAT] AS " & [Mo06] & "Spend, " _
               & "tblHFIMR357PlannPurchasesExtract.Period07 AS " & [Mo07] &
"Qty, [Period07]*[SMAT] AS " & [Mo07] & "Spend, " _
               & "tblHFIMR357PlannPurchasesExtract.Period08 AS " & [Mo08] &
"Qty, [Period08]*[SMAT] AS " & [Mo08] & "Spend, " _
               & "tblHFIMR357PlannPurchasesExtract.Period09 AS " & [Mo09] &
"Qty, [Period09]*[SMAT] AS " & [Mo09] & "Spend, " _
               & "tblHFIMR357PlannPurchasesExtract.Period10 AS " & [Mo10] &
"Qty, [Period10]*[SMAT] AS " & [Mo10] & "Spend, " _
               & "tblHFIMR357PlannPurchasesExtract.Period11 AS " & [Mo11] &
"Qty, [Period11]*[SMAT] AS " & [Mo11] & "Spend, " _
               & "tblHFIMR357PlannPurchasesExtract.Period12 AS " & [Mo12] &
"Qty, [Period12]*[SMAT] AS " & [Mo12] & "Spend, " _
               & "tblHFIMR357PlannPurchasesExtract.Period13 AS " & [Mo13] &
"Qty, [Period13]*[SMAT] AS " & [Mo13] & "Spend "
   CreateSQL04 = "FROM tblComMgr RIGHT JOIN
tblHFIMR357PlannPurchasesExtract ON tblComMgr.ComMgrID =
tblHFIMR357PlannPurchasesExtract.BYR;"
   CreateSQL_All = CreateSQL01 & CreateSQL02 & CreateSQL03 & CreateSQL04
   Set dbsCurrent = CurrentDb
   Set qryTest = dbsCurrent.QueryDefs("qryHFIMR357Excel")
   qryTest.SQL = CreateSQL_All

End Sub

Let me clarify.  The yellow arrow is on the line with the FROM statement.