IIF Statement Using Do Not Equal 2

[awl]

I have a report grouped by Examinations and Job Titles. The four types of job titles in my report I need: Accountants, Drivers, Other – OCONUS, and All Others.

My IIF statement in my query is as follows:

TypeOfJobTitle: IIf([PEAccountantsY]=True,"Accountants",
IIf([PPEJobTitle]="Accountant","Accountants",
IIf([OEJobTitle]="Accountant","Accountants",
IIf([DeployJobTitle]="Accountant","Accountants",
IIf([PSWSPJobTitle]="Accountant","Accountants",
IIf([PEDriverY]=True,"Drivers",
IIf([PPEJobTitle]="Driver","Drivers",
IIf([OEJobTitle]="Driver","Drivers",
IIf([DeployJobTitle]="Driver","Drivers",
IIf([PSWSPJobTitle]="Driver","Drivers","All Others&quot)))))))))

This is Ok. Except, my problem: if [PPEJobTitle] and [DeployJobTitle] do not equal Accountant or Driver, then I want the occupation to be “Other – OCONUS”, since these occupations fall under the Overseas Examinations. I have used either “Or” or “<>” elements and have resulted in either a –1 or the Drivers become over-ridden by Other – OCONUS in the report. I’m not sure if an “IIF Statement” can contain a “<>” or “Or” element. Is there a way to accomplish my goal in the same IIF Statement? Curtis….

 

[TonyJollans]

Hi awl,

To answer your question, you should be able to say ..

IIf(Field <> Value, ResultIfTrue, ResultIfFalse)

However, you have explicitly checked for both [PPEJobTitle] and [DeployJobTitle] being &quot;Accountant&quot; and &quot;Driver&quot; amongst other explicit checks. All that is left is what you currently group under &quot;All Others&quot;. No amount of further checking of these conditions is going to return a result. There must be another check you have to make.

Enjoy,
Tony

 

[Golom]

Here's a structure that does what you said you want

IIf(   [PEAccountantsY]=True 
    OR [PPEJobTitle]="Accountant" 
    OR [OEJobTitle]="Accountant" 
    OR [DeployJobTitle]="Accountant" 
    OR [PSWSPJobTitle]="Accountant","Accountants",

IIf(   [PEDriverY]=True
    OR [PPEJobTitle]="Driver"
    OR [OEJobTitle]="Driver"
    OR [DeployJobTitle]="Driver"
    OR [PSWSPJobTitle]="Driver","Drivers",

IIF(    [PPEJobTitle]<>&quot;Accountant&quot; AND [DeployJobTitle]<>&quot;Accountant&quot;
    AND [PPEJobTitle]<>&quot;Driver&quot; AND [DeployJobTitle]<>&quot;Driver&quot;, &quot;Other – OCONUS&quot;, 

&quot;All Others&quot;)))

You should note however that you will never get the &quot;All Others&quot; result because the two fields being tested in the third IIF will return TRUE in one of the IIF statements because you are testing exactly opposite conditions. (i.e. if &quot;A=X&quot; is FALSE then &quot;A<>X&quot; must be TRUE).

 

[awl]

Mr. Tony Jollans and Mr. Golom: I thank both of you for your comments/suggestions. I did copy Mr. Golom's structure and the end result is still the same as both of you had commented. I did have to do an “extra check”. I created a 2nd IIF Statement field comparing the result of the 1st IIF Statement field results and was able to achieve the wanted results. Thank you for your professional responses. Curtis…