Handling the Conditional: Using the IIf Function

Let's imagine we have an Access table containing a list of academic exam results.  There is a field for Subject and a field for the percentage Result , but we do not have one telling us whether each percentage result is deemed a Pass or Fail.  So what is the best way to 'store' this missing information?

Figure 1: An Access Table containing
Subject and Result fields.

We might be tempted create such a Pass/Fail field, but this is generally considered bad database design practice: since it is possible to calculate whether the student passed or failed on the basis of the percentage result, we would be storing redundant data.  Creating a Query would be a much better option.  However, we need to display whether the result is a Pass or a Fail, so simply entering a criteria to filter out all results above or below a certain percentage is not going to do the job: this would only provide us with a list of Passes or a separate list of Fails.  We just want one column stating Pass or Fail.

A great way of doing this task would be to use the IIf function as a new calculated column within the Query.  This will enable us to display a value indicating whether the exam has been passed or failed.  The IIf function allows us to specify a conditional expression (in a similar way to a query criteria), but then to go on and specify a value to be displayed based on whether the result of the expression happens to be true or false.  In our case we want the expression to determine whether the value contained in a percentage result field is, say,  greater than 50%, and if it is, display "PASS", or else display "FAIL".

The IIf function is constructed as follows:

IIf(Conditional Expression,  True_Condition, False_Condition)

The first parameter we pass for this function is the conditional expression.  In our case this would be Result > 50.  The Second parameter we pass is the string value which appears if the condition is true (for us this would be "PASS"); and the third parameter is the string value which appears if the condition is false (for us this would be "FAIL").  As such we would construct our own particular IIf function like this:

IIf([Result] > 50,  "Pass", "Fail")

We shall enter the this expression into our query as follows:

Figure 2: The IIf Function has been entered into the third column using the alias Pass.

As you can see we have entered our IIf function in the last column on the right.  We have used the alias "Pass" to describe the data to be displayed in this column.  (For more information about alias's and using functions in Access Queries, please see this explanation in relation to the DateDiff Function).  Just remember to separate the alias name from the function with a colon as shown in Figure 2 above.

Now lets run the query and see what we get:

Figure 3: the results of our query to calculate whether
a student has passed or failed an exam.

As you can see, the IIf function has correctly assigned a Pass or Fail based on the percentage Result attained.