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.
3. Lecture table contains Lecture Name, Phone Number, Available (check box)
4. Mark Sheet Contains Grade.
Relationships among table and query (Evaluation query) can be seen in the screenshot below
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.
When we will run the query, status of the student can be seen in Final field.
This is a very simple way of using conditional operator, it would become more complicated when applying formula over complex conditions.
how do you use the nested iif to create grade a,b,c,d,e,f
The typo IFF is throughout the document. It should read IIF.
There's a typo in the title.