Function Size Limitations

[Ken Little]

Is there any limit to how long or how many IIF..Thens can be used in a
function? I have 11 in one function and am getting the error message:
"Compile Error: Argument not Optional." It highlights the last IIF in the
formula.

Ken

 

[Hans-Christian Francke]

There are no limitations AFAIK. Compile error reffers to the function that
is highlighted. Check that the arguments passed to the function are correct.
Also, note that the If...Then...Else statement is by far preffered over the
IIF function and much faster.

 

[John Vinson]

Is there any limit to how long or how many IIF..Thens can be used in a
function? I have 11 in one function and am getting the error message:
"Compile Error: Argument not Optional." It highlights the last IIF in the
formula.

Ken

There is a limit to how long a calculated field expression can be:
<looking under Specifications in Help>

Number of characters in a cell in the query design grid 1,024

Deeply nested IIF's are VERY inefficient as well: consider using the
Switch() or Choose() functions (open the VBA editor and look for Help
on these functions), or perhaps creating a table which can be joined
in your query if you're essentially using the IIF's to do a lookup.

 

[david epsom dot com dot au]

Notes:

1) Access will not always point to the correct place on a
line if there is an error. Look at the whole line. Break
it up into If...Then...Else...End If... to make the error
easier to find.

2) In an SQL statement, only the selected branches of the
IIF statement are evaluated. The branches that are not
evaluated do not need to contain valid code/sql. When
testing, you must make sure that all branches are tested.

(david)