Please, help me me with this SELECT statement!!!

[Alira]

Hi everybody!

I've been strugling with this query for some time when I first created it. And after slight modification it does not work again! And I don't know what to do...

So, here it is:

The working one:
SQL = "SELECT HumanRes.WeekNumber AS [Week_#], " & _
"HumanRes.HeadCount AS H_Count, " & _
"HumanRes.LostTime, " & _
"ModuleTime.ModulesCount AS Mod_Qty, " & _
"Format(H_Count*0.9028,'0.0') AS Capacity, " & _
"Format((H_Count*32.5-[LostTime])*(1/36),'0.0') AS Actual_Cty, " & _
"Format((Mod_Qty/(H_Count * 0.9028)) * 100,'0.0') AS Gen_Eff, " & _
"Format((Mod_Qty/Actual_Cty)*100,'0.0') AS Actual_Eff " & _
"FROM (SELECT Sum(Format(([Module].[Ship Date]-[Module].[Kit-Up Date])*24,'0.0')) " & _
"AS [Cycle Time], Count(*) AS ModulesCount, DatePart('ww',[Module].[Ship Date])-1 " & _
"AS WeekNumber FROM [Module] WHERE (DatePart('yyyy',[Module].[Ship Date])= " & Year & " " & _
"AND (Module.[Ship Date] Is Not Null)) " & _
"GROUP BY DatePart('ww',[Module].[Ship Date])-1) AS ModuleTime " & _
"INNER JOIN (SELECT HumanResourses.WeekNumber, HumanResourses.HeadCount, " & _
"HumanResourses.LostTime From HumanResourses " & _
"WHERE (((HumanResourses.YearNumber)= " & Year & &quot)) AS HumanRes " & _
"ON ModuleTime.WeekNumber = HumanRes.WeekNumber " & _
"WHERE (((HumanRes.WeekNumber) Between " & StartWeek & " And " & EndWeek & &quot)"

This one does not work giving me error "SELECT' operator is missing or incorrect reserved word"
If tags allowed modified parts will be in bold.

SQL = "SELECT HumanRes.WeekNumber AS [Week_#], " & _
"HumanRes.HeadCount AS H_Count, " & _
"HumanRes.LostTime, HumanRes.WeekHours " & _
"ModuleTime.ModulesCount AS Mod_Qty, " & _
"Format(H_Count*(HumanRes.WeekHours/36),'0.0') AS Capacity, " & _
&quot;Format((H_Count*<b>HumanRes.WeekHours</b>-LostTime)*(1/36),'0.0') AS Actual_Cty, &quot; & _
&quot;Format((Mod_Qty/(H_Count * (HumanRes.WeekHours/36))) * 100,'0.0') AS Gen_Eff, &quot; & _
&quot;Format((Mod_Qty/Actual_Cty)*100,'0.0') AS Actual_Eff &quot; & _
&quot;FROM (SELECT Sum(Format((Module.[Ship Date]-Module.[Kit-Up Date])*24,'0.0')) &quot; & _
&quot;AS [Cycle Time], Count(*) AS ModulesCount, DatePart('ww',Module.[Ship Date])-1 &quot; & _
&quot;AS WeekNumber FROM Module WHERE (DatePart('yyyy',Module.[Ship Date])= &quot; & Year & &quot; &quot; & _
&quot;AND (Module.[Ship Date] Is Not Null)) &quot; & _
&quot;GROUP BY DatePart('ww',Module.[Ship Date])-1) AS ModuleTime &quot; & _
&quot;INNER JOIN (SELECT HumanResourses.WeekNumber, HumanResourses.HeadCount, &quot; & _
&quot;HumanResourses.LostTime, HumanResourses.WeekHours From HumanResourses &quot; & _
&quot;WHERE (((HumanResourses.YearNumber)= &quot; & Year & &quot)) AS HumanRes &quot; & _
&quot;ON ModuleTime.WeekNumber = HumanRes.WeekNumber &quot; & _
&quot;WHERE (((HumanRes.WeekNumber) Between &quot; & StartWeek & &quot; And &quot; & EndWeek & &quot)&quot;

 

[IronIke]

It looks like you might be missing a , after HumanRes.WeekHours.

_
&quot;HumanRes.LostTime, HumanRes.WeekHours &quot; & _
&quot;ModuleTime.ModulesCount AS Mod_Qty, &quot; & _

 

[Alira]

Thanks a lot!!!
I don't know why I didn't see this...