Solved

Access Query Expression too complex in query expression

Posted on 2009-04-14
7
492 Views
Last Modified: 2013-11-29
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;

Open in new window

0
Comment
Question by:afsfire
7 Comments
 
LVL 15

Expert Comment

by:MNelson831
ID: 24142410
You left out half of the NZ() statements so the query thinks you are trying to do MANY nested if null then loops.

try this instead:

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_DHApartmentHeadLayout],0)+Nz([fld_DHAptPipeLayoutSchedule],0)+Nz([fld_DHGarageHeadLayout],0)+Nz([fld_DHGarPipeLayoutSchedule],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_DHInsertsCanoutsTags],0)+Nz([fld_DHCleanUp],0)+Nz([fld_DHPlanCheck],0)+Nz([fld_DHTainingPeerReview],0)+Nz([fld_ChangeOrder],0)+Nz([fld_As-Builts],0) AS DHDesignHoursTotalsByID
FROM tbl_DesignHours
GROUP BY fld_JobID;
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 24142437
try 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;

Open in new window

0
 

Author Comment

by:afsfire
ID: 24142444
That is teling me Syntax Error (missing operator) in query expression
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 44

Expert Comment

by:GRayL
ID: 24142466
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_DHApartmentHeadLayout],0)+
Nz([fld_DHAptPipeLayoutSchedule],0)+
Nz([fld_DHGarageHeadLayout],0)+
Nz([fld_DHGarPipeLayoutSchedule],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_DHInsertsCanoutsTags],0)+
Nz([fld_DHCleanUp],0)+
Nz([fld_DHPlanCheck],0)+
Nz([fld_DHTainingPeerReview],0)+
Nz([fld_ChangeOrder],0)+
Nz([fld_As-Builts],0)) AS DHDesignHoursTotalsByID
FROM tbl_DesignHours
GROUP BY fld_JobID;
0
 

Author Closing Comment

by:afsfire
ID: 31570151
Thanks it worked great
0
 
LVL 15

Expert Comment

by:MNelson831
ID: 24142557
My bad.  Brain fart between nz in Access and IsNull in SQL
0
 

Author Comment

by:afsfire
ID: 24142571
Thanks anyway... still learning alot from the help from all.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now