Query too Complex

[Nosdron]

I have a nested IIf Statement in a query and then when I try to do another calculated nested IIf statement dependent on the first nested IIf statement I get the error message "Query too Complex".

Can I do the above use a nested Iif statement on another nested IIf statement in access as you would be able to in Excel?

 

[blom0344]

Hello Nosdron,

Instead of using a nested IIF structure, you might want to use the CASE strategy.

See Access help file for examples. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[Nosdron]

By Case Stratergy do you me a Select Case Statement ? If so, dont I use it in code. How do I link the cose to the query ?

 

[blom0344]

Hello Nosdron,

Case is accepted ANSI SQL, but does not seem to work directly in Access, you can look up the way you use a defined function in SQL by checking out the following thread:

Thread701-295388

in the Microsoft: Access Queries and Jet SQL forum

An alternative for IIF that does work directly in Jet SQL is the Switch function (a bit like the Decode function in ORACLE)

T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[Nosdron]

Thanks.

I have tried the switch statment and the query is still too complex so I will try this "Roll your own" principle. I will probably have problems though as I am not an experienced programmer. I'll come back to you and let you know how I go on.

Thanks

Angela

 

[SQLWilts]

Hi,
You can always finish that query at the point just before it gets "too complex" and save it. Then create another query over the top of it and carry on - only way that I have solved this problem myself. I know that it isn't particulary efficient, but it works!!

Best of Luck.

 

[blom0344]

Hello Angela , (Tony),

I reread the original thread and stopped to think about the part that says:

"IIf statement dependent on the first nested IIf statement".

I think it boils down what you mean by 'dependent'. Simply nesting IIF statements can be done down to a certain number of repetitions before the engine gives an error message.
But nesting IIF does not imply dependancy.
But if you build something that is actually dependent on the OUTCOME of another IIF (can't think of an example right now) you may run into the limitation of SQL.
Tony's solution (query upon query) may then be the way to go , to avoid self-referencing in the statement. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[Nosdron]

I completed the function case statement in code like you told me and it worked. Thank You.

The application I am setting up is very complex though and many calculations depend on the outcome of others. I am only at an eary stage in designing the application and I am already gettting the "Complex" errors. I have split my calculations so far into smaller queries like Tony said what I am concerened about is thet each of these queries is related to a different quote number and at some point all the relevant information calculated will have to come together to do further calculations but if I try to do a query with information form the smaller queries I get an error because each individual query references the quote number so I either get an error message saying it doesnt know which query it is reading this field from or in the query result I get numerous results for each quote number instead of just one ?

Any ideas ?

Angela

 

[blom0344]

Hello Angela,

It is a bit like groping in the dark to give you further advice without having a look at what you are trying to build
If you want me to look at some examples you run into that give you a hard time, mail them to me and I'll look into them. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com