is there a finite number of functions that can be used in an expressio

[Susanne45]

is there a finite number of functions that can be used in an expression?

Expr 1 = Start Date Of Warranty
Expr 5 = End Date Of Warranty

What I started with (11 hard coded dates)

Expr2: IIf([Expr5]<#1/1/2002#,0,IIf([Expr1]>Now(),0,IIf([Expr1]<#1/1/2002# And [Expr5] Between Now() And #12/31/2002#,Now()-#1/1/2002#,IIf([Expr1]<#1/1/2002# And [Expr5]>#12/31/2002#,Now()-#1/1/2002#,IIf([Expr1]<#1/1/2002# And [Expr5]<Now(),[Expr5]-#1/1/2002#,IIf([Expr1]>#1/1/2002# And [Expr5]>#12/31/2002#,Now()-[Expr1]))))))

Works (8 functions)

Expr9: IIf([Expr5]<DateSerial(Year(Date()),&quot;1&quot;,&quot;1&quot,0,IIf([Expr1]>Now(),0,IIf([Expr1]<DateSerial(Year(Date()),&quot;1&quot;,&quot;1&quot And [Expr5] Between Now() And DateSerial(Year(Date()),&quot;12&quot;,&quot;31&quot,Now()-DateSerial(Year(Date()),&quot;1&quot;,&quot;1&quot,IIf([Expr1]<DateSerial(Year(Date()),&quot;1&quot;,&quot;1&quot And [Expr5]>DateSerial(Year(Date()),&quot;12&quot;,&quot;31&quot,Now()-#1/1/2002#,IIf([Expr1]<DateSerial(Year(Date()),&quot;1&quot;,&quot;1&quot And [Expr5]<Now(),[Expr5]-#1/1/2002#,IIf([Expr1]>#1/1/2002# And [Expr5]>DateSerial(Year(Date()),&quot;12&quot;,&quot;31&quot,Now()-[Expr1]))))))

Does Not Work (9 functions)

Expr9: IIf([Expr5]<DateSerial(Year(Date()),&quot;1&quot;,&quot;1&quot,0,IIf([Expr1]>Now(),0,IIf([Expr1]<DateSerial(Year(Date()),&quot;1&quot;,&quot;1&quot And [Expr5] Between Now() And DateSerial(Year(Date()),&quot;12&quot;,&quot;31&quot,Now()-DateSerial(Year(Date()),&quot;1&quot;,&quot;1&quot,IIf([Expr1]<DateSerial(Year(Date()),&quot;1&quot;,&quot;1&quot And [Expr5]>DateSerial(Year(Date()),&quot;12&quot;,&quot;31&quot,Now()-#1/1/2002#,IIf([Expr1]<DateSerial(Year(Date()),&quot;1&quot;,&quot;1&quot And [Expr5]<Now(),[Expr5]-DateSerial(Year(Date()),&quot;1&quot;,&quot;1&quot
,IIf([Expr1]>#1/1/2002# And [Expr5]>DateSerial(Year(Date()),&quot;12&quot;,&quot;31&quot,Now()-[Expr1]))))))

Works (8 functions) Switching Places

Expr9: IIf([Expr5]<#1/1/2002#,0,IIf([Expr1]>Now(),0,IIf([Expr1]<DateSerial(Year(Date()),&quot;1&quot;,&quot;1&quot And [Expr5] Between Now() And DateSerial(Year(Date()),&quot;12&quot;,&quot;31&quot,Now()-DateSerial(Year(Date()),&quot;1&quot;,&quot;1&quot,IIf([Expr1]<DateSerial(Year(Date()),&quot;1&quot;,&quot;1&quot And [Expr5]>DateSerial(Year(Date()),&quot;12&quot;,&quot;31&quot,Now()-#1/1/2002#,IIf([Expr1]<DateSerial(Year(Date()),&quot;1&quot;,&quot;1&quot And [Expr5]<Now(),[Expr5]-DateSerial(Year(Date()),&quot;1&quot;,&quot;1&quot,IIf([Expr1]>#1/1/2002# And [Expr5]>DateSerial(Year(Date()),&quot;12&quot;,&quot;31&quot,Now()-[Expr1]))))))

 

[cmmrfrds]

You don't say what your error is?

My guess, it is probably related to the number of characters that you can put in a single variable. Does it go over 255?

 

[scriverb]

Don't know exactly what you problem is because cmmrfrds states you didn't state what the error was or what your erroneous errors were but nexted IIF's are very difficult to control and keep straight in programming. With expressions this complex I know that the Switch function works really well. And, there is an unliminted number of expressions that can be used. Checkout ACCESS help for the Switch statment for the syntax and the explanation of the function. You can also used the IIF function within the switch where it will help in your analysis of the data.

Let me know if that helps. Bob Scriver

 

[MichaelRed]

Going one step beyond Bob's suggestion, put the 'stuff' into a procedure (function) and detangle the nesting with some simple logic. I did run the char count, and thte one I used had 525 characters. I understand that the 'limit' for the length of an SQL statement depends on the mechanisim used to generate it. At a glance, I can;t really tell how this one was generated, but I suspect it is the query grid thinggy. This imposes a different limit depending on the version of Ms. A. under discussion. I think that Ms. A is well over ~ 500 characters of your posted query, but going backwards I'm not as sure. There are NUMEROUS limits on queries in most all of the relational db (SQL) implementations. One which might be related is that any recordset (query building / result) can have no more than 255 'fields' involved - BUT, the term 'field' in this context includes indicies and other 'attributes'. If you have considerably more than this expression in the overall query it might also be the issue. Again, moving the calculation to a procedure (in this instance) would reduce the field count and relieve some part of the problem.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over