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.

2. Courses table contains Course Name, Credit Hrs.

courses

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

lecture

4. Mark Sheet Contains Grade.

marksheet

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

query relationships

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.

IIF

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

Final query

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

قالب وردپرس

About Alyse Kalish

Alyse Kalish
As an Associate Editor for The SalesJobInfo, Alyse is proud to prove that yes, English majors can change the world. She calls many places home, including Illinois where she grew up and the small town of Hamilton where she attended Colgate University, but she was born to be a New Yorker. In addition to being an avid writer, Alyse loves to dance, both professionally and while waiting for the subway.

Leave a Reply

Your email address will not be published. Required fields are marked *