Nesting IIF Function

[Alexandra]

IIf([DlxRmSeason]="Spring Rate","$85.00 "," " I need to nest 3 IIF functions into a single expression in a form. Much like an IF then statement. What is the correct nesting positioning and syntax for performing this expression?

 

[Alexandra]

Here is my nested IIF function in an Access 2000 form
=IIf([DlxRmSeason]="Spring Rate","$85.00 "," ",IIf([DlxRmSeason]="Summer Rate","$109.00 "," ",IIf([DlxRmSeason]="Fall-Winter Rate","$75.00 "," "))))

It works on the first IIF, but not in the nested ones.

 

[Tim Ferguson]

IIf([DlxRmSeason]="Spring Rate","$85.00 "," " I need to nest 3 IIF
functions into a single expression in a form. Much like an IF then
statement. What is the correct nesting positioning and syntax for
performing this expression?

IIf(strNum = "One", 1,
IIf(strNum = "Two", 2,
IIf(strNum = "Three", 3, "More than three")))

Physical layout, indenting and so on are obviously up to you: as long as
you and your successors can read it easily.

Tim F

 

[Bob]

----- Alexandra wrote: -----

Here is my nested IIF function in an Access 2000 form
=IIf([DlxRmSeason]="Spring Rate","$85.00 "," ",IIf([DlxRmSeason]="Summer Rate","$109.00 "," ",IIf([DlxRmSeason]="Fall-Winter Rate","$75.00 "," "))))

It works on the first IIF, but not in the nested ones.

Try:
=IIf([DlxRmSeason]="Spring Rate","$85.00 ",IIf([DlxRmSeason]="Summer Rate","$109.00 ",IIf([DlxRmSeason]="Fall-Winter Rate","$75.00 "," "))))

By the way, IIF is a particularly slow construction: the system has to evaluate all of the cases even if the first is true.

 

[Steve Schapel]

Alexandra,

I agree with Bob's suggestion (except for his parentheses counting).
However, a couple of questions...

- Do you really want the data in the form to be text? It seems to me
it would be preferable, for a number of reasons, to use numerical
values, and use the Format property of the textbox on the form to
display as currency.

- Do you really want a blank space in the textbox if DlxRmSeason is
not one of the offered options. I would normally make it Null myself.

Thus...
=IIf([DlxRmSeason]="Spring Rate",85,IIf([DlxRmSeason]="Summer
Rate",109,IIf([DlxRmSeason]="Fall-Winter Rate",75,Null)))

- However, you might actually find that the Switch function is more
applicable than IIf...
=Switch([DlxRmSeason]="Spring Rate",85,[DlxRmSeason]="Summer
Rate",109,[DlxRmSeason]="Fall-Winter Rate",75)

- Steve Schapel, Microsoft Access MVP