complicated iif statement

[deb]

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])))

 

[xenou]

Hi,
These four fields all use different criteria (ie. submitters org unit, or responsible coordinators org, or process owners historical org, or CAPAStatus). I don't see how they can be combined without losing information, so I guess the question becomes what information should be used as the criteria if there is going to one field called RespGroup. That should help lead the way forward.
ξ

 

[HiTechCoach]

A User Defined Function UDF) can be called from within a Query.

You could create a Public Function in a standard code module that returns the string you want to display in your query.

The function could return the four calculated values concatenated into a single string.


You could also try using a fifth calculated field that used the previous four:

MyNewClacedField: RespGroupM1M3 & "-" & RespGroupM4M5 & "-" & RespGroupM5M11 & "-" & RespGroupClosed