MS Access 2010: Query With IFF Function

In MS Access, the IIF function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE. IIF function returns one of two arguments depending on the evaluation of an expression just like as we use logic in IF-ELSE structure. It evaluates both true part and false part, even though it returns only one of them. This post covers creating a simple query with IFF function.

Launch Access 2010, create tables and make query for puling out desired data from tables. For demonstration purpose, I have made four tables and a query, as shown in the screenshots below.

1. AddictiveTips table contains information such as: Student Name, Address, Phone No.

addictivetipstable MS Access 2010: Query With IFF FunctionView in gallery

2. Courses table contains Course Name, Credit Hrs.

courses MS Access 2010: Query With IFF FunctionView in gallery

3. Lecture table contains Lecture Name, Phone Number, Available (check box)

lectureView in gallery

4. Mark Sheet Contains Grade.

marksheetView in gallery

Relationships among table and query (Evaluation query) can be seen in the screenshot below

query relationshipsView in gallery

At end of the query Field row (adjacent to Grade) we will put a single line of code containing IFF function that would generate the status of the student corresponding to the Grade secured.

Final: IIf([Grade]=”D”,”Fail”,”Pass”)

This line of code would add column naming Final in query, with a condition that if grade secured is equal to D. the status of student will be Fail.

IIFView in gallery

When we will run the query, status of the student can be seen in Final field.

Final queryView in gallery

This is a very simple way of using conditional operator, it would become more complicated when applying formula over complex conditions.

  • asa

    There's a typo in the title.

  • Carol

    The typo IFF is throughout the document. It should read IIF.

  • kobhy

    how do you use the nested iif to create grade a,b,c,d,e,f