IIF

[Bell1991]

I am returning values of a lookup table for a report.. The problem the values are to large to display on the report, so i would like to return abbreviations for the values..

For example:

Select txtTypeDesc, 
    iif(txtTypeDesc LIKE "High", "HIGH","")
    iif(txtTypeDesc LIKE "LOW", "LOW', "")
    iif(txtTypeDesc LIKE "MED", "MED", "") as txtTypeDesc

I know the code above will not work.. but is what i would to do possibel?

 

[PHV]

Brute force method:
SELECT UCase(Left([txtTypeDesc], 3)) AS AbrevType

Another way:
SELECT IIf([txtTypeDesc] Like 'High*','HIGH'
,IIf([txtTypeDesc] Like 'Low*','LOW'
,IIf([txtTypeDesc] Like 'Med*','MED'
,UCase(Left([txtTypeDesc], 3))))) AS AbrevType

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

Once I hit two IIf()s, I either create a function or use Switch():

Select txtTypeDesc, 
    Switch(Left(txtTypeDesc,3) = "Hig", "HIGH",
           Left(txtTypeDesc,3)="LOW", "LOW',
           Left(txtTypeDesc,3)="MED", "MED") as txtTypeDescShort

You should also provide a unique "alias" for the calculated column as PHV suggested.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]