troubleshooting Question

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

Avatar of PeterBaileyUk
PeterBaileyUk asked on
Microsoft AccessSQL
3 Comments3 Solutions70 ViewsLast Modified:
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)));"
eeexample.PNG
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 3 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros