Iif

Iif(FirstClause, FirstClauseTrueValue, ..., LastClause, LastClauseTrueValue, FalseValue)

Returns either a value defined for the first Boolean condition that is True, or the last value if none of these conditions is True.
This function can accept any number of arguments.

EXAMPLE

Use ‘Customer Title’ column to create Male and Female data. If the Customer Title is ‘Mr’ set the output to be Male. If the Customer Title is not ‘Mr’ set the output to be Female.

Calculated Field:

Iif([Customer Title] = 'Mr', 'Male','Female')

RESULT:

IsNull

IsNull(Value)

Returns True if the specified Value is NULL.

EXAMPLE

Use IsNull to check which row does not have data in the Name column:

Calculated Field:

IsNull([Name])

OUTPUT

IsNull(Value1, Value2)

Returns the second operand if the first operand is null, otherwise, the first operand is returned.

EXAMPLE:

Use IsNull to return ‘Name to be Inserted’ where a name does not exist.

Calculated Field:

IsNull([Name], ‘Name to be Inserted’)

OUTPUT

IsNullOrEmpty

IsNullOrEmpty(String)

Returns True if the specified String object is NULL or an empty string; otherwise, False is returned.

EXAMPLE

Use IsNullOrEmpty to check which row does not have data in the Sale Price column:

Calculated Field:

IsNullOrEmpty([Sale Price])

OUTPUT

ToBoolean

ToBoolean(Value)

Converts Value to an equivalent Boolean value.

EXAMPLE

Use ToBoolean to set a row to False when there is no data:

Calculated Field:

ToBoolean([Sale Price])

OUTPUT

Combining Functions in Calculated Fields

If you want to create more complex calculated fields, you can combine different functions. 

Let’s create a calculated field which returns a text field that says ‘Free Product’ when the Sale Price is set to True and return a value of an empty space when the Sale Price set to False.

We need to start with our Iif statement:

Iif( *condition* ,*when true* , *when false*)

We need out condition to return true and false values, rather than the content of the column we’re querying. 

For this we will use IsNull:

IsNull([Sale Price])

Now we know the two functions we need, let’s combine them:

Iif(IsNull([Sale Price]) = 'True', 'Free Product','')

Our output should look like this: