Query is Too Complex

[aftabn10]

Hi, I have written the following formula, but get an error saying the "Query is too complex".

Test: IIf([Count Advisors]<3,0,(IIf([Q Rank]>([Count Advisors]/3*2),3,0)))

Any ideas why?

 

[JANR]

A shot in the dark:

Test: IIf([Count Advisors]<3,0,(IIf([Q Rank]>(([Count Advisors]/3)*2),3,0)))

JR

 

[DCrake]

Are the fields you are attempting to evaluate true field values or are they computations that exist in an underlying query?

Tip:
In most instances IIF statements evaluates both the true and false condtions before it makes the decision which path to take. It may be more prudent to write a simple function and use that in stead:

IIf([Count Advisors]<3,0,(IIf([Q Rank]>([Count Advisors]/3*2),3,0)))

Replace with

Public Function Test(Advisors As Integer, Rank As Intetger) As Integer

If Advisors < 3 Then
    Test = 0
Else
    If Rank > (Advisors/3)*2 Then
       Test = 3
    Else
       Test = 0
End If

End Function

Then in your query use

MyTest:Test([Count Advisors],[Q Rank])

Don't forget to save the function in a standard module first.

 

[aftabn10]

JANR, Thanks but unfortunately that still gives me the same error..

 

[aftabn10]

DCrake, the fields that i am using are actually results from other queries that I have ran? So could this be the problem?

Btw, thanks for your answer, i will give that a go and let you know how i get on.

 

[DCrake]

What is actuially happening is that your calculated field is being processed

First when the underlying query is referenced
Next when evaluating the True statement in the first IIF
Again when evaluating the True statement of the nested IIF
And again when Access decides which part to use. So all this processing is becoming complex for Access to handle.

 

[aftabn10]

DCrake, thanks for your explanation, i have saved the module and ran the query but get an #Error in the results...

 

[DCrake]

This may be that at least one result cannot be computated due to a null value. Does your underlying query return a valid value for each side of the equasion? Try using Nz().