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

[J]

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")))

 

[Chaim]

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

 

[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.

 

[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.