IIf And or Statement

[djai695]

I have two fields:
[PERSONNEL_TYPE] which contains either an R, U, P
[SP_OBJ_GRP] which contains multiple number codes (031, 047, 089)

The two fields are coming off of a union query, and some of the fields show the codes, but other rows show a "0" because of the way I setup the query.

I want to setup an expression that performs the following three arguments:

If [PERSONNEL_TYPE] = "R" and [SP_OBJ_GRP] = "0", then make the [SP_OBJ_GRP] show "001" or show the default codes that are listed.

If [PERSONNEL_TYPE] = "U" and [SP_OBJ_GRP] = "0", then make the [SP_OBJ_GRP] show "004" or show the default codes that are listed.

If [PERSONNEL_TYPE] = "P" and [SP_OBJ_GRP] = "0", then make the [SP_OBJ_GRP] show "005" or show the default codes that are listed.


This is what I tried as an expression, but I'm having problems with it. Any help would be greatly appreciated.


OC: IIf(([PERSONNEL_TYPE]="R" And ([SP_OBJ_GRP]="0","001", IIf([PERSONNEL_TYPE]="U" And ([SP_OBJ_GRP]="0","004",IIf(([PERSONNEL_TYPE]="P" And ([SP_OBJ_GRP]="0","005",[SP_OBJ_GRP])))

 

[Geotch]

Instead of writing long if statements I set up a cross reference table which can easily be changed by an end user if necessary.

I've uploaded a sample, look at qryFinal and the column Final If Statement which has your answer.

 

[djai695]

Thank you, I will check this out tomorrow.