Multiple "OR" functions

[Special_Egg]

Hello forum,
I'm attempting to write a query that will return a value based on letters parsed out from a unique identifier. So, if the identifier contains the letters xr, then return the value X-Ray or if the identifier contains the letters mr, then return the value MRI, or if the identifier contains the letters ir, then return the value Interventional Radiology, and so on. I know this will work because I did it years ago, I just can't remember the syntax.

UNIT: IIf([CleanStudyMoves].[Identifier] Like "*XR*",'X-Ray') Or IIf([CleanStudyMoves].[Identifier] Like "*IR*",'Interventional Radiology')

It returns a value of -1 when I run it. Any help is appreciated.

[moke123]

dont use OR. Do a Search for Nested IIF's.

[June7]

Nested IIf()'s or a Switch() function.

How many 'so on'? AFAIK, nested IIf() is limited to 7 levels.

[Special_Egg]

The nested IIF's resolved my problem. The code below works great. As you can see, nested IIF's are not limited to 7 levels.

MODALITY: IIf([CleanStudyMoves].[Identifier] Like "*XR*",'X-Ray',IIf([CleanStudyMoves].[Identifier] Like "*IR*",'Interventional Radiology',IIf([CleanStudyMoves].[Identifier] Like "*US*",'Ultra Sound',IIf([CleanStudyMoves].[Identifier] Like "*CT*",'Computerized Topography',IIf([CleanStudyMoves].[Identifier] Like "*OR*",'Orthopedics',IIf([CleanStudyMoves].[Identifier] Like "*MA*",'Mammography',IIf([CleanStudyMoves].[Identifier] Like "*MR*",'MRI',IIf([CleanStudyMoves].[Identifier] Like "*NM*",'Nuc Med',IIf([CleanStudyMoves].[Identifier] Like "*BP*",'DEXA',Null)))))))))

[June7]

Okay, maybe confused with nested subforms.

Might explore the Switch() function. I think it would simplify the expression.

MODALITY: Switch([Identifier] Like "*XR*",'X-Ray', [Identifier] Like "*IR*",'Interventional Radiology', [Identifier] Like "*US*",'Ultra Sound', [Identifier] Like "*CT*",'Computerized Topography', [Identifier] Like "*OR*",'Orthopedics', [Identifier] Like "*MA*",'Mammography', [Identifier] Like "*MR*",'MRI', [Identifier] Like "*NM*",'Nuc Med', [Identifier] Like "*BP*",'DEXA', True,"N/A")

Should "Topography" be "Tomography"

[Special_Egg]

Yes, switch simplified the expression. Thank you very much for your help June. And thanks for catching my typo. LOL