IIf()

This is one of the most versatile and useful functions in FoxPro. It allows you to put the equivalent of an IF-ELSE-ENDIF right in the middle of any expression.

Usage

uResult = IIF( lCondition, uThenResult, uElseResult)

Parameter	Value	Meaning
lCondition	.T.	Returns the value of uThenResult.
lCondition	.F. or .NULL.	Returns the value of uElseResult.
uThenResult	Anything except General	If lCondition is true, returns the value of this expression.
uElseResult	Anything except General	If lCondition is not true, returns the value of this expression.
uResult	Anything except General	The value of either uThenResult or uElseResult.

This function got more complicated with the addition of nulls. It used to be simple—if lCondition is true, return uThenResult; if lCondition is false, return uElseResult. But now there's the chance that lCondition is null. In that case, IIF() returns uElseResult.IIF() is useful whenever you need to specify an expression and can't use IF-ELSE-ENDIF. We use it a lot in reports and queries.

Example

? IIF(x>y, "larger", "smaller")
IIF(paid, "X", " ")

The second example might appear in a report. It prints an "X" for those who've paid and leaves an empty space for those who haven't.When combined with the aggregate functions, IIF() can do some pretty powerful things in a query. This example counts how many TasTrade employees are 30 and over (should we trust them?) and how many are under 30.

SELECT SUM(IIF(YEAR(Birth_Date)+30 <= YEAR(DATE()),1,0)) AS Over30, ;
       SUM(IIF(YEAR(Birth_Date)+30 > YEAR(DATE()),1,0)) AS Under30;
  FROM TasTrade!Employee ;
  INTO CURSOR AgeCount

IIF()'s can be nested, just like IF...ELSE...ENDIF structures, and execute with nearly the speed of a single command. Down this path leads madness, or at least "cryptocode," as our friend Mac Rubel calls it. Try to avoid having to debug lines of code that are indecipherable even to their author:

lReturnValue=IIF(CDOW(dDate)="Tuesday","Yes",IIF(CMONTH(dDate)="March", ;
"Maybe",IIF(YEAR(dDate)%4=0 and YEAR(dDate)%100 <> 0, "No","Idunno")))

Yuck. Keep It Simple, Stupid.