What is the IIf Function
What is the IIf Function?
First of all, the IIF function is short for the Immediate If function.
The syntax for the IIF function is the following:
IIf (expression, truepart, falsepart)
This syntax accomplishes the following:
IIF the expression equates to true, then the value in the ‘truepart’ is returned; otherwise the value in the ‘falsepart’ is returned.
To put this into a real life example:
If you added the following to a blank column in a query, where [Number1] and [Number2] are field names for a field that contains number values.
Result: IIf([Number1]>=[Number2],"Number1 is higher or equal","Number1 is lower ")
If the field [Number1] is greater or equal to field [Number2], i.e. True, then the words “Number1 is higher or equal” would be displayed in the query when it is run (which is the first part or the True section of the IIF statement). However, if the field [Number1] is less than field [Number2], i.e. False, then the words “Number 1 is lower” would be displayed.
Note: If you are copying and pasting straight from this website, you will need to change the talking marks, as they are not compatible with Microsoft Access and you will get an error. i.e. once you have pasted the formula above, just delete the talking marks (") and add them and add them back in.
Below are couple of images to demonstrate the example I have given above:
The Image 1 is what the IIF function looks in the design view of a query, and Image 2, what the result looks like for that same query.
Image 1 - IIF Function Query Design view example.
Image 2 - IIF Function Query Result example.
One more thing to mention, when talking about IIF functions. You can leave the false part, blank, to do this, change:
Result: IIf([Number1]>=[Number2],"Number1 is higher or equal","Number1 is lower ")
to
Result: IIf([Number1]>=[Number2],"Number1 is higher or equal")
Now instead of displaying 'Number1 is lower' in the result (image 2), a null value (blank field) will be displayed.
Hope you have found the tip What is the IIf Function useful.
A couple of other tips around this topic that you may find useful.
And another example of a simple IIF function, used in conjunction with conditional formatting and the DSum function