Bill Warren
asked on
Access Query Expression too complex in query expression
I have this query that I'm building that keeps giving me the Expression too complex in query expression. Attached is the SQL code are there too many columns involved? Is there a way to get around this?
SELECT fld_JobID,ID, Sum(Nz([fld_DHBidPrep])+(Nz([fld_DHHeadBreakdown])+(Nz([fld_DHRebid])+(Nz([fld_DHWACADSetUP])+(Nz([fld_DHHBCADSetUp])+(Nz([fld_DHPrepWork])+(Nz([fld_DHRFIDetails])+(Nz([fld_DHApartmentHeadLayout])+(Nz([fld_DHAptPipeLayoutSchedule])+(Nz([fld_DHGarageHeadLayout])+(Nz([fld_DHGarPipeLayoutSchedule])+(Nz([fld_DHStandpipes])+(Nz([fld_DHRFIs])+(Nz([fld_DHCalcTime])+(Nz([fld_DHStocklisting])+(Nz([fld_DHSitePlan])+(Nz([fld_GeneralNotes])+(Nz([fld_DHSwayBrace])+(Nz([fld_DHInsertsCanoutsTags])+(Nz([fld_DHCleanUp])+(Nz([fld_DHPlanCheck])+(Nz([fld_DHTainingPeerReview])+(Nz([fld_ChangeOrder])+(Nz([fld_As-Builts]))))))))))))))))))))))))) AS DHDesignHoursTotalsByID
FROM tbl_DesignHours
GROUP BY fld_JobID;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That is teling me Syntax Error (missing operator) in query expression
I think it should look like this:
SELECT fld_JobID,ID, Sum(
Nz([fld_DHBidPrep],0)+
Nz([fld_DHHeadBreakdown],0 )+
Nz([fld_DHRebid],0)+
Nz([fld_DHWACADSetUP],0)+
Nz([fld_DHHBCADSetUp],0)+
Nz([fld_DHPrepWork],0)+
Nz(fld_DHRFIDetails],0)+
Nz([fld_DHApartmentHeadLay out],0)+
Nz([fld_DHAptPipeLayoutSch edule],0)+
Nz([fld_DHGarageHeadLayout ],0)+
Nz([fld_DHGarPipeLayoutSch edule],0)+
Nz([fld_DHStandpipes],0)+
Nz([fld_DHRFIs],0)+
Nz([fld_DHCalcTime],0)+
Nz([fld_DHStocklisting],0) +
Nz([fld_DHSitePlan],0)+
Nz([fld_GeneralNotes],0)+
Nz([fld_DHSwayBrace],0)+
Nz([fld_DHInsertsCanoutsTa gs],0)+
Nz([fld_DHCleanUp],0)+
Nz([fld_DHPlanCheck],0)+
Nz([fld_DHTainingPeerRevie w],0)+
Nz([fld_ChangeOrder],0)+
Nz([fld_As-Builts],0)) AS DHDesignHoursTotalsByID
FROM tbl_DesignHours
GROUP BY fld_JobID;
SELECT fld_JobID,ID, Sum(
Nz([fld_DHBidPrep],0)+
Nz([fld_DHHeadBreakdown],0
Nz([fld_DHRebid],0)+
Nz([fld_DHWACADSetUP],0)+
Nz([fld_DHHBCADSetUp],0)+
Nz([fld_DHPrepWork],0)+
Nz(fld_DHRFIDetails],0)+
Nz([fld_DHApartmentHeadLay
Nz([fld_DHAptPipeLayoutSch
Nz([fld_DHGarageHeadLayout
Nz([fld_DHGarPipeLayoutSch
Nz([fld_DHStandpipes],0)+
Nz([fld_DHRFIs],0)+
Nz([fld_DHCalcTime],0)+
Nz([fld_DHStocklisting],0)
Nz([fld_DHSitePlan],0)+
Nz([fld_GeneralNotes],0)+
Nz([fld_DHSwayBrace],0)+
Nz([fld_DHInsertsCanoutsTa
Nz([fld_DHCleanUp],0)+
Nz([fld_DHPlanCheck],0)+
Nz([fld_DHTainingPeerRevie
Nz([fld_ChangeOrder],0)+
Nz([fld_As-Builts],0)) AS DHDesignHoursTotalsByID
FROM tbl_DesignHours
GROUP BY fld_JobID;
ASKER
Thanks it worked great
My bad. Brain fart between nz in Access and IsNull in SQL
ASKER
Thanks anyway... still learning alot from the help from all.
try this instead:
SELECT
fld_JobID,
ID,
Sum(Nz([fld_DHBidPrep],0)+
FROM tbl_DesignHours
GROUP BY fld_JobID;