Access sql runs as a query but not from docmd.sql

I have a query which runs from the query grid fine. I need to make the sql dynamic but i cannot get it to run from vb it reports back error as attached and not sure why.

    Case 4
        ' all events
        DoCmd.RunSQL "SELECT v.CapCode, Min(v.ChangeYearMonth) AS minYM, Max(v.ChangeYearMonth) AS maxYM, First(v.BHPPrev) AS Tprev," _
        & " Last(v.BHPChange) AS Tchange, Round(Abs([BHPPrev]-[BHPChange])) AS DataVariance, CapCurrentCodes.CAPid_CAPcat, [CapToMvris-CW].CapCode1_1, [CapToMvris-CW].MvrisCode, SMMT.[MVRIS CODE], SMMT.[CALC BHP], Round(Abs([BHPchange]-[calc bhp])) AS [Variance to Match], [CapToMvris-CW].CapCategory_1" _
        & " FROM (((SELECT DISTINCT [CapCode], ChangeYearMonth, BHPPrev, BHPChange FROM TblBHP)  AS v LEFT JOIN CapCurrentCodes ON v.CapCode = CapCurrentCodes.CAPid_CAPcat) LEFT JOIN [CapToMvris-CW] ON CapCurrentCodes.CapVehicleID = [CapToMvris-CW].CapCode1_1) LEFT JOIN SMMT ON [CapToMvris-CW].MvrisCode = SMMT.[MVRIS CODE]" _
        & " GROUP BY v.CapCode, Round(Abs([BHPPrev]-[BHPChange])), CapCurrentCodes.CAPid_CAPcat, [CapToMvris-CW].CapCode1_1, [CapToMvris-CW].MvrisCode, SMMT.[MVRIS CODE], SMMT.[CALC BHP], Round(Abs([BHPchange]-[calc bhp])), [CapToMvris-CW].CapCategory_1" _
        & " HAVING (((First(v.BHPPrev)) Is Not Null Or (First(v.BHPPrev)) Like ""0"") And ((Last(v.BHPChange)) Is Not Null Or (Last(v.BHPChange)) Like ""0"") And ((CapCurrentCodes.CAPid_CAPcat) Is Not Null) And (([CapToMvris-CW].CapCategory_1)=IIf(Right([capcode],1)=""C"",""Car"",IIf(Right([capcode],1)=""L"",""LCV"",""Motorcycle"")) Or ([CapToMvris-CW].CapCategory_1) Is Null) And ((Nz(First(v.BHPPrev),""""))<>Last(v.BHPChange)));"

Select allOpen in new window

eeexample.PNG