Access 2010
How can the below, be combined into one statement for a query?
It currently creates 4 fields but I only want one called RespGroup.
RespGroupM1M3: IIf([MS]="1" Or [MS]="2" Or [MS]="3",IIf(IsNull([OriginatorHistoricalOrg]) And IsNull([OriginatingOrg]),[SubmittersOrgUnit],IIf(Not IsNull([OriginatorHistoricalOrg]),[OriginatorHistoricalOrg],[OriginatingOrg])))
RespGroupM4M5: IIf([MS]="4" Or [MS]="5",IIf(IsNull([ResponsibleCoordinatorsHistOrg]) And IsNull([ResponsibleCoordinatorsOrg]),"Missing",IIf(Not IsNull([ResponsibleCoordinatorsHistOrg]),[ResponsibleCoordinatorsHistOrg],[ResponsibleCoordinatorsOrg])))
RespGroupM5M11: IIf([MS]="5" Or [MS]="6" Or [MS]="7" Or [MS]="8" Or [MS]="9" Or [MS]="10" Or [MS]="11",IIf(IsNull([ProcessOwnersHistOrg]) And IsNull([ProcessOwnersOrg]),"Missing",IIf(Not IsNull([ProcessOwnersHistOrg]),[ProcessOwnersHistOrg],[ProcessOwnersOrg])))
RespGroupClosed: IIf([CAPAStatus]="Closed",IIf(IsNull([ProcessOwnersHistOrg]) And IsNull([ProcessOwnersOrg]),"Missing",IIf(Not IsNull([ProcessOwnersHistOrg]),[ProcessOwnersHistOrg],[ProcessOwnersOrg])))
How can the below, be combined into one statement for a query?
It currently creates 4 fields but I only want one called RespGroup.
RespGroupM1M3: IIf([MS]="1" Or [MS]="2" Or [MS]="3",IIf(IsNull([OriginatorHistoricalOrg]) And IsNull([OriginatingOrg]),[SubmittersOrgUnit],IIf(Not IsNull([OriginatorHistoricalOrg]),[OriginatorHistoricalOrg],[OriginatingOrg])))
RespGroupM4M5: IIf([MS]="4" Or [MS]="5",IIf(IsNull([ResponsibleCoordinatorsHistOrg]) And IsNull([ResponsibleCoordinatorsOrg]),"Missing",IIf(Not IsNull([ResponsibleCoordinatorsHistOrg]),[ResponsibleCoordinatorsHistOrg],[ResponsibleCoordinatorsOrg])))
RespGroupM5M11: IIf([MS]="5" Or [MS]="6" Or [MS]="7" Or [MS]="8" Or [MS]="9" Or [MS]="10" Or [MS]="11",IIf(IsNull([ProcessOwnersHistOrg]) And IsNull([ProcessOwnersOrg]),"Missing",IIf(Not IsNull([ProcessOwnersHistOrg]),[ProcessOwnersHistOrg],[ProcessOwnersOrg])))
RespGroupClosed: IIf([CAPAStatus]="Closed",IIf(IsNull([ProcessOwnersHistOrg]) And IsNull([ProcessOwnersOrg]),"Missing",IIf(Not IsNull([ProcessOwnersHistOrg]),[ProcessOwnersHistOrg],[ProcessOwnersOrg])))