IIF expression

[king07]

Okay

Maybe i am totally confused but here is initially what i have and what i want to do.

I currently have a db for carrer development-- i have three fields

Last Review Date
Next Discussion Due
Status

My next discussion due is on the fly-- i did a dateadd (by one year) from the last review date to get the current result for the next disussion due--

here is what i want to do with the status and am confused with how to do it--
i want the status to have three options-- completed,overdue and upcoming--
i want these to be autogenerated but am unable to find what expression i need-- as the IIf expression only gives the option of two possible results--

so how would i go about doing the expression with having three possible outcomes?

 

[KeithG]

You can nest IFF functions inside each other

IIF([Next Discussion Due]<Date(),"OverDue",IIf([Next Discussion Due]>Date(),"UpComing",""))

the above is an example for you. How are you dong to determine if it is completed?

 

[king07]

first off thanks-- for putting in the simple comma-- i tired everything

and for getting the status of completed-- i have been stumped at how i would actually go about getting the result--

essentially if the last review date is in the fiscal year (which runs Nov 1-Oct 31) then it is completed

could it not be done that if the last review date is equal to the current year then it would show up as completed?



this stuff is hard!

 

[Moniker]

Instead of nesting IIF functions, use a SWITCH function.

=SWITCH(your_parameter_1,value_if_parameter_1_is_true,
your_parameter_2,value_if_parameter_2_is_true,
your_parameter_x,value_if_parameter_x_is_true)

This allows you to test three (or many more) possible outcomes and assign a value based on the first parameter that is true. For example, if your_parameter_1 is TRUE, your variable will be assigned to value_if_parameter_1_is_true. If your_parameter_1 is not TRUE, then your_parameter_2 is evaluated, and so on, until a TRUE value is reached. In case none of the parameters are TRUE, add a TRUE clause at the end of the switch function, like this:

=SWITCH(your_parameter_1,value_if_parameter_1_is_true,
your_parameter_2,value_if_parameter_2_is_true,
your_parameter_x,value_if_parameter_x_is_true,
TRUE,value_when_no_parameters_were_true)

That forces the very last comparison to be true (since it's equal to TRUE) and can be used for error trapping as well.

 

[king07]

Moniker

First off thanks for the help. The switch expression looks like the possible way to go-- my only question .... how does it work with dates? It seems that it is easiest to work with this function with numbers but my fields have dates is there anyway around this??

 

[Moniker]

SWITCH (MyDateField=#01/10/2007#,this_value_if_equal to today,
MyDateField<#01/10/2007,this_value_if_before_today,
MyDateField>#01/10/2007,this_value_if_after_today,
TRUE, this_value_if_cant_determine_date)

 

[king07]

Moniker

Thanks for the help

Call me stupid-- but when i use the switch expression like you suggested-- i enter in dates when prompted but by some weird reason the date that comes up in the status is now the day before the current day. Can i somehow change the dates to have the options show up (i.e. completed,overdue or upcoming)??


Now when i use the following IIf function
Status: IIf([Next Discussion Due]<Date(),"OverDue",IIf([Next Discussion Due]>Date(),"UpComing",""))

All the proper overdues and upcoming show up but how would i properly nest for having completed show up??


I apologize for the questions-- this is seeming to be more complex than i thought with this darn expression.....