Can Access do this: If this is true in Field 1, And this Or this Or this is true in Field 2, And this Or this is true in Field 3...

[Churchy LaFemme]

Having trouble and maybe because I've overreaching.

I want a Yes/No column based on checking for (or excluding) values from three columns.

IFF([Geography]="Canada" And [Level]="Intern" Or [Level]="Trainee" Or [Level]="Associate" And [Rating]="Five" Or [Rating]="Four","Fasttrack","Review")

My guess is that Access wants to work the two ANDs first. But I'm not sure. This goes a little better when I do <> for the conditions and switch the truepart with the falsepart.

I can do it with one error: the last condition in one of the ands will be ignored. In the example above, either "Associate" or "Five" would be ignored.

I can trick it a little bit by adding a nonsense value after the And. (e.g., And [Level]="Nonsense" or [Level]="Intern" ...."
But the trick only gets me perfect results for one field.

Will Access go through field by field and return a result for only the rows where all each of the OR conditions for that row is met?

 

[Russell Hauf]

You'll want to put parentheses around your ORs, something like this:

IFF([Geography]="Canada" And ([Level]="Intern" Or [Level]="Trainee" Or [Level]="Associate") And ([Rating]="Five" Or [Rating]="Four"),"Fasttrack","Review")

 

[Churchy LaFemme]

This is working for some sets and not for others. But now that I know it is possible to group the conditions for a field in (), then I should be able to crack it.

(There is nothing different about the sets where it works. Some lines are for people who are not ready to fasttrack or review because we aren't looking at their level and or their rating.)

Thanks!

 

[Joe4]

I am sure it is probably just a typo (because it wouldn't work at all otherwise), but it is IIF, not IFF.

You can also clean it up a bit like this, using "in" instead of all the OR statements:

IIF([Geography]="Canada" And ([Level] in ("Intern","Trainee","Associate")) And ([Rating] in ("Five","Four")),"Fasttrack","Review")

If this does not do exactly what you want, please lay out all your conditions in plain English, and give us an example that is not working the way you want.