I have a problem with my Query in Access

[legs00]

I AM WRITING A QUERY IN ACCESS AND I AM HAVING PROBLEMS RUNNING THE QUERY BELOW.

SELECT
[activities].[Perc_Complete] AS Perc_Comp,
[activities].[StillToBeDone],
[activities].[WBSID],
[activities].[ProjectID],
[activities].[WBS],
[activities].[End_Date],
[activities].[LOE],
[activities].[Start_Date],
[activities].[Budget_Heures],
getspent([wbsid]) AS Spent,

IIf( [loe]=0,
null,
IIf( Now()>[end_date],
100,
0
)
) AS Perc_Comppp,

iif( (abs(int([End_Date]-[Start_Date]))=0) OR (abs(int([End_Date]-[Start_Date]))<0),
0,
abs(int( (Now() - [Start_Date]) / ([End_Date] - [Start_Date])))
) AS Final_Date,

IIf( ISNULL(LOE),
Null,
IIf( 0 > ( IIf( [Budget_Heures] > 0,
IIf( 0 > nz( [Final_Date],0),
(1 - 0 ^ [LOE]) * [Budget_Heures],
(1 - nz([Final_Date], 0) ^ [LOE]) * [Budget_Heures]
),
IIf( 0 > nz([Final_Date],0),
(1 - 0 ^ [LOE]) * [Spent],
(1 - nz([Final_Date], 0) ^ [LOE]) * [Spent]
)
)
),
0,
IIf( [Budget_Heures] > 0,
IIf( 0 > nz([Final_Date],0,),
(1 - 0 ^ [LOE]) * [Budget_Heures],
(1 - nz([Final_Date],0) ^ [LOE]) * [Budget_Heures]
),
IIf( 0 > nz( [Final_Date],0),
(1 - 0 ^ [LOE]) * [Spent],
(1 - nz([Final_Date],0) ^ [LOE]) * [Spent]
)
),
)
) AS StillToBeDonepp,

IIf( IsNull([LOE]),
IIf( ([StillToBeDone]<>&quot;&quot or (nz([StillToBeDone],0) + [Spent] <> 0),
iif( [Spent] + IIf( [StillToBeDone]=&quot;&quot;,
0,
[StillToBeDone]) = 0,
0,
[Spent] / ([Spent]+ [StillToBeDone])
),
IIf( (IIf( [Perc_Comp]=&quot;&quot;,
0,
[Perc_Comp]
) ) > 10,
IIf( 100 < (IIf([Perc_Comp]=&quot;&quot;,0,[Perc_Comp])),
100,
[Perc_Comp]
),
IIf( [Budget_Heures] = 0,
&quot;&quot;,
IIf( [Spent] = 0,
&quot;&quot;,
IIf( 20 < (iif([Budget_Heures]=0,
0,
[Spent]/[Budget_Heures]
)
),
20,
[Spent]/[Budget_Heures]
)
)
)
)
),
IIf( ([StillToBeDonepp]<>&quot;&quot or ( [StillToBeDonepp] + [Spent]<>0),
iif( [Spent] + IIf([StillToBeDonepp]=&quot;&quot;,
0,
[StillToBeDonepp]
) = 0,
0,
abs(int([Spent] / ([Spent]+ [StillToBeDonepp])))
),
IIF( IIf([Perc_Comppp]=&quot;&quot;,0,[Perc_Comppp]) > 10,
IIF( 100 < (IIf([Perc_Comppp]=&quot;&quot;,0,[Perc_Comppp])),
100,
[Perc_Comppp]
),
IIf( [Budget_Heures] = 0,
&quot;&quot;,
IIf( [Spent] = 0,
&quot;&quot;,
IIF( 20 < (iif([Budget_Heures]=0,0,[Spent]/[Budget_Heures])),
20,
[Spent] / [Budget_Heures]
)
)
)
)
)
) AS Perc_Complet,

IIf( IsNull([LOE]),
IIf( [Budget_Heures]=0,
[Spent]+nz([StillToBeDone],0),
IIf( [Spent]=0,
IIf( nz([StillToBeDone],0)<>0,
nz([StillToBeDone],0),
IIf(isnull( [Perc_Complet]),
[Budget_Heures]*(1-[Perc_Complet]),
[Budget_Heures]
)
),
IIf([Perc_Comppp]=&quot;&quot;,0,[Spent]/([Perc_Comppp]/100))
)
),
IIf( [Budget_Heures]=0,
[Spent]+nz([StillToBeDonepp],0),
IIf( [Spent]=0,
IIf( nz([StillToBeDonepp],0)<>0,
nz([StillToBeDonepp],0),
IIf( isnull(nz([Perc_Comppp],0)),
[Budget_Heures]*(1-[Perc_Complet]),
[Budget_Heures]
)
),
IIf(isnull([Perc_Comppp]) or [Perc_Comppp]=0,0, [Spent]/([Perc_Comppp]/100))
)
)
) AS Revised_Estimate,

[Revised_Estimate] - [Spent] AS StillToBeDoneCal,



NOW THE QUERY RUNS FINE UP UNTIL THIS POINT. AFTER THIS POINT IF I ADD THE REST BELOW AND RUN THE QUERY, I GET A MESSAGEBOX SAYING THAT &quot;QUERY IS TOO COMPLEX&quot;


IIf( IsNull([Perc_Complet]),
&quot; &quot;,
IIf( [Perc_Complet] = 0,
IIf( 0 > ([Budget_Heures] - [Revised_Estimate]),
0,
( [Budget_Heures] - [Revised_Estimate])
),
([Perc_Complet]/100) * [Budget_Heures]
)
) AS Earned_Hours, [Revised_Estimate] - [Spent] AS StillToBeDoneCal, IIf( [Budget_Heures] = 0,
&quot; &quot;,
IIf( [Spent] = 0,
&quot; &quot;,
[Earned_Hours] / [Spent]
)
) AS CPI
FROM activities;


NOW PLEASE COULD YOU TELL ME WHAT I SHOULD DO. SHOULD I WRITE A FUNCTION IN VBA AND CALL IT IN MY QUERY? IF YOU CAN GIVE ME A DIRECTION TO GO TOWARDS IT WOULD BE VERY HELPFUL.

THANK-YOU
PATTY

 

[tlbroadbent]

You may need to create functions to replace the complex IIF functions in your query. Reducing the number of function calls may help. Shortening table and column names may help. Removing white space may help.

Microsoft published an article about this error.

http://support.microsoft.com/support/kb/articles/Q103/4/29.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.