How many IIF statements can I use in a expression within a query?

[Guest]

I am using an expression in a query that runs a few IIF statements. I would
like to add one more IIF statement to it. below is the orignial expression
from the query.

EstCost:
IIf([actEstimatedCost]>0,[actEstimatedCost],IIf([actEstimatedCost]=0 And
[CalculatedField]=False,0,[actActualYTD]+[actCommitted]+[Forecast],if([actestimatedcost]<0,[actannualbudget])))

I would like to add "IIF([actEstimatedCost]<0,[actEstimatedCost]"

any suggestions?

thanks in advance,

Jim

 

[fredg]

I am using an expression in a query that runs a few IIF statements. I would
like to add one more IIF statement to it. below is the orignial expression
from the query.

EstCost:
IIf([actEstimatedCost]>0,[actEstimatedCost],IIf([actEstimatedCost]=0 And
[CalculatedField]=False,0,[actActualYTD]+[actCommitted]+[Forecast],if([actestimatedcost]<0,[actannualbudget])))

I would like to add "IIF([actEstimatedCost]<0,[actEstimatedCost]"

any suggestions?

thanks in advance,

Jim

You can have more, however the current statement is not written
correctly.

You are OK up to here:
IIf([actEstimatedCost]>0,[actEstimatedCost],IIf([actEstimatedCost]=0
And [CalculatedField]=False,0,
You then added
[actActualYTD]+[actCommitted]+[Forecast]
as the False value, which would have been OK (had you ended the IIF's
there) except for the fact that you then add (using If instead of IIF)
if([actestimatedcost]<0,[actannualbudget])))

I can't rewrite it for you as I don't know you what to do with this
part:
[actActualYTD]+[actCommitted]+[Forecast]

Genericly, the code would be like this:

=IIf([FieldA]=Something,"True Part A",IIf([FieldB] = Something,"True
Part B",IIf([FieldC] = Something,"True Part C","False Part")))

 

[Guest]

Why not try it and see what happens?

 

[Dale Fye]

Jim,

When it starts getting dicey like this, I just write a function, and pass
the function all the values it needs. I do this primarily because as I
understand it, Access evaluates all of the elements of an IIF statement,
including the test, and the True and False values. So Access will evaluate
all of your tests, even if the first test is true. I cannot remember where
I heard this, but you can do a simple test by pasting the following into
your immediate window and hitting return. It is also significantly easier
to modify a user defined function, and document the rules you used than it
is to make sense of multiple nested IIF() functions.

?IIF(TRUE, 1, IIF(val(Null) > 0, 2, 3))

Note that since you passed the IIF a value of TRUE, you would think that
Access would only process until it passed the test, but that is not the
case. You might want to look at the Switch function instead of creating
your own function. Switch evaluates a series of expressions and returns the
associated value of the first expression that is true.

EstCost: Switch([actEstimatedCost]>0, [actEstimatedCost], [actEstimatedCost]
= 0 AND [CalculatedField] = False, 0)

HTH
Dale

 

[John Spencer (MVP)]

Dale,

VBA evaluates all parts of the IIF statement.

Access in a query (JET SQL), as far as I can tell only evaluates only until it
gets a result. Of course, since I don't have access to the underlying code,
that is more or less a guess.

Jim,

When it starts getting dicey like this, I just write a function, and pass
the function all the values it needs. I do this primarily because as I
understand it, Access evaluates all of the elements of an IIF statement,
including the test, and the True and False values. So Access will evaluate
all of your tests, even if the first test is true. I cannot remember where
I heard this, but you can do a simple test by pasting the following into
your immediate window and hitting return. It is also significantly easier
to modify a user defined function, and document the rules you used than it
is to make sense of multiple nested IIF() functions.

?IIF(TRUE, 1, IIF(val(Null) > 0, 2, 3))

Note that since you passed the IIF a value of TRUE, you would think that
Access would only process until it passed the test, but that is not the
case. You might want to look at the Switch function instead of creating
your own function. Switch evaluates a series of expressions and returns the
associated value of the first expression that is true.

EstCost: Switch([actEstimatedCost]>0, [actEstimatedCost], [actEstimatedCost]
= 0 AND [CalculatedField] = False, 0)

HTH
Dale

I am using an expression in a query that runs a few IIF statements. I would
like to add one more IIF statement to it. below is the orignial expression
from the query.

EstCost:
IIf([actEstimatedCost]>0,[actEstimatedCost],IIf([actEstimatedCost]=0 And
[CalculatedField]=False,0,[actActualYTD]+[actCommitted]+[Forecast],if([actestimatedcost]<0,[actannualbudget])))

I would like to add "IIF([actEstimatedCost]<0,[actEstimatedCost]"

any suggestions?

thanks in advance,

Jim

 

[Dale Fye]

Thanks, John.

It had never occurred to me that the way VBA and JET handle that would be
different.

Dale

Dale,

VBA evaluates all parts of the IIF statement.

Access in a query (JET SQL), as far as I can tell only evaluates only
until it
gets a result. Of course, since I don't have access to the underlying
code,
that is more or less a guess.

Jim,

When it starts getting dicey like this, I just write a function, and pass
the function all the values it needs. I do this primarily because as I
understand it, Access evaluates all of the elements of an IIF statement,
including the test, and the True and False values. So Access will
evaluate
all of your tests, even if the first test is true. I cannot remember
where
I heard this, but you can do a simple test by pasting the following into
your immediate window and hitting return. It is also significantly
easier
to modify a user defined function, and document the rules you used than
it
is to make sense of multiple nested IIF() functions.

?IIF(TRUE, 1, IIF(val(Null) > 0, 2, 3))

Note that since you passed the IIF a value of TRUE, you would think that
Access would only process until it passed the test, but that is not the
case. You might want to look at the Switch function instead of creating
your own function. Switch evaluates a series of expressions and returns
the
associated value of the first expression that is true.

EstCost: Switch([actEstimatedCost]>0, [actEstimatedCost],
[actEstimatedCost]
= 0 AND [CalculatedField] = False, 0)

HTH
Dale

I am using an expression in a query that runs a few IIF statements. I
would
like to add one more IIF statement to it. below is the orignial
expression
from the query.

EstCost:
IIf([actEstimatedCost]>0,[actEstimatedCost],IIf([actEstimatedCost]=0
And
[CalculatedField]=False,0,[actActualYTD]+[actCommitted]+[Forecast],if([actestimatedcost]<0,[actannualbudget])))

I would like to add "IIF([actEstimatedCost]<0,[actEstimatedCost]"

any suggestions?

thanks in advance,

Jim

 

[[MVP] S.Clark]

There may be a limit, but you'll probably a string length limit before that
happens. Just know that this query will be very slow and difficult to debug
in the future.

FYI... You shouldn't need to add a 3rd, as 1 and 3 result in the same
outcome.


--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

 

[Terrell Miller]

There may be a limit, but you'll probably a string length limit before that
happens. Just know that this query will be very slow and difficult to debug
in the future.

you can nest up to seven IIf statements in one expression. Or if they're
independent ( Iif(x=1,1,0) + Iif(y<12,1,0) + Iif(z <<> null,1,0)) you
can use as many as you need up to the character l;imit of an expression,
which I don't have at hand but is in the thousands of characters.

HTH,

--
Terrell Miller
(e-mail address removed)

"Suddenly, after nearly 30 years of scorn, Prog is cool again".
-Entertainment Weekly

 

[Brendan Reynolds]

IMHO the real, practical limitation on nested IIFs is readability - the
expression will become an unreadable and unmaintainable mess long before it
hits any purely technical limitation.

 

[Terrell Miller]

IMHO the real, practical limitation on nested IIFs is readability - the
expression will become an unreadable and unmaintainable mess long before it
hits any purely technical limitation.

one easy workaround for that is to assemble (or edit) the expression in
WordPad, where you can add as many line returns as you need for readability.

Then all you do is remove the line breaks, copy it back into the
expression builder pane in Access and see if it runs. If not, repeat the
process.

Sometimes the most efficient solutions are the low-tech ones...

--
Terrell Miller
(e-mail address removed)

"Suddenly, after nearly 30 years of scorn, Prog is cool again".
-Entertainment Weekly

 

[Brendan Reynolds]

I think it would take a lot more than a few line returns to make seven
nested IIFs readable, Terrell. Fortunately, there are usually alternatives -
Switch or Choose can often be used in place of nested IIFs.

 

[Terrell Miller]

I think it would take a lot more than a few line returns to make seven
nested IIFs readable, Terrell. Fortunately, there are usually alternatives -
Switch or Choose can often be used in place of nested IIFs.

actually, I've had more typo-glitches with Switch than with Iif. It's
harder to tell where the "pairs" are since they're not enclosed in
parens like an Iif. And Choose of course can only take numeric values,
so you can't do the equivalent of

Iif([STATE]='KS',1,0)

Iif isn't teh greatest structure in the world (that would be Select
Case, not that you can use it in a query), but it gets the job done.

--
Terrell Miller
(e-mail address removed)

"Suddenly, after nearly 30 years of scorn, Prog is cool again".
-Entertainment Weekly