Hacker’s Guide to Visual FoxPro
An irreverent look at how Visual FoxPro really works. Tells you the inside scoop on every command, function, property, event and method of Visual FoxPro.
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. |
|
.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.