Conditional Formula by using IIF

[Shameen]

Hi,



I am struggling with the following condition on to the query table. Can any one please help to complete the following conditions. I have tried number of times, but it returns strange results.

DisplayText: IIf([Mark Obtained - Practical]<5,"FAIL",IIf([Mark Obtained - Practical]>5<6.5,"Grade C - Pass")).............?

Conditions to include:

• When score <4.9 ‘Fail’
• When score >5<6.5 ‘Grade C – Pass’
• When score>6.6<7.5 ‘Grade B – Pass’
• When score >7.6<8.5 ‘Grade A – Pass’
• When score >8.6=10 ‘Grade A+ - Pass’

Regards,
Shameen

[pbaldy]

Your syntax is wrong, but since the tests are applied in order, simply test for less than 4.9, less than 6.5, etc.

[CJ_London]

I concur with Paul's comments but also suggest using the switch function rather than the iif function for readability

[ranman256]

IIf([Mark Obtained - Practical]>5 and [Mark Obtained - Practical]<6.5,"c","")

or make a custom function for queries to use. Paste the code in a module.....
In the query :
GetGrade([Mark Obtained - Practical])

Function getgrade(pvPts)

Select case pvPts
  Case  >5 and <6.5 
       GetGrade =   ‘Grade C – Pass’
Case >6.6 and <7.5
     GetGrade =‘Grade B – Pass’
When score >7.6and <8.5
      GetGrade =‘Grade A – Pass’
Case  >8.6 and <=10
    GetGrade =‘Grade A+ - Pass’
Case else
  GetGrade =‘Fail’
end select
end function