Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

Susanne45

Technical User
Sep 26, 2002
4
US
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]))))))












 
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?
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top