Nested If in Expression Builder

[dance]

Is it possible to have a nested "if" expression in the expression builder? If so, what is the syntax? I tried what seemed a logical syntax, but the field turned blank upon running the query.

 

[Golom]

Nested IIF statements are quite possible but I suggest that you work in SQL view rather than the query builder. Something like
[tt]
IIF ([fldA] < 10, 1,
IIF ([fldA] < 20, 2,
IIF ([fldA] < 30, 3,
IIF ([fldA] < 40, 4, 5 )
[/tt]
is fairly easy to read displayed this way but
[tt]
IIF ([fldA] < 10, 1, IIF ([fldA] < 20, 2, IIF ([fldA] < 30, 3, IIF ([fldA] < 40, 4, 5 )
[/tt]
as it would be displayed in Query Builder is a bit obscure.

 

[dhookom]

As an IIf() gets nested several layers deep, it may be better to use Switch() for ease of maintenance. Also, too many nested IIf()s suggest you should be using data values rather than hard-coded values in expressions.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[BNPMike]

As Duane implies, if you have a lot of if 'a then b' you might as well take advantage of the fact you have a relational database and create a table of decode values which can the just join to.

 

[dance]

Thank you. What is Switch?
What is a "table of decode values which can the just join to

 

[WillRiley]

Switch is a function available in most reporting tools...Crystal, Access, VBA etc

The syntax is

Switch(expression1,var1,expression2,var2,....etc)

i.e.

Switch({dmd.type} <> "credit",
(({dstt.avg_bal}*(({?Base}-{@CrRate})/100))/365)*{dstt.num_days},
{dmd.type}="credit",
(({dstt.avg_bal}*(({?Base}-{@CrRate}-0.25)/100))/365)*{dstt.num_days})

Hope this helps,
Will

 

[dhookom]

Another example of Switch()
Switch([Age]<1,"Infant",[Age]>1 AND [Age]<=3, "Toddler", [Age]>3 and [Age]<=12,"Kid",...etc...)
These expressions are in pairs. When the first expression of a pair is true, the function returns the second part of the pair.
In this situation a lookup table would be:
tblAges
==============
MinAge MaxAge Title
0 1 Infant
2 3 Toddler
4 12 Kid
etc
This would allow you to maintain the titles in data where it should be, not in a complex expression where it shouldn't be.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.