Substitution for table values

[Darby Holmes]

I have a table imported from a database - using
the 'linked table' feature - and thus I am unable to
change the values to acronyms. I need to
shorten/abbreviate the values (not column headers) (e.g.
New Requirements needs to be NR) in either the Query or
Report. I tried to use IIF and IF although this hasn't
seem to function despite my best attempt - see below.
Please help.

Thanks

=IIF([Reason]="New Requirements", "NR",IIF([Reason]
="Design Change","DC"),IIF([Reason]="Missed
Requirement","MR"),IIF([Reason]="Implementation
Change","IC"),IIF([Reason]="Requirement
Clarification","RqC"), [Reason]))

 

[Mark]

I can't see anything wrong in your reading of the
situation. I've attempted the same from a table
containing one field "Reason". In the Query Design Grid,
the field contains the following:

Expr1: IIf([reason]="New Requirements","NR",IIf([reason]
="Design Change","DC",IIf([reason]="Missed
Requirement","MR",IIf([reason]="Implementation
Change","IC",IIf([reason]="Requirement
Clarification","RqC",[Reason])))))

The results are displaying the abbreviations as expected.
Have you missed any brackets from the expression?

 

[Les]

Hi,
John Spencer gave you the better solution under your
post in Queries. If you want to proceed with this
solution, please note that Mark has his parentheses placed
correctly, while your expression doesn't. I also noted
this under your other post in the Queries section.

Good luck.