Nest IIf

[Guest]

I have a field named "SEGCODE", in a query design view.
In a calculated field named "NEWSEGCODE", I need to write the following
expression.
If SEGCODE field has "A", write "A1"
Else If SEGCODE field has "B", write "B3"
Else If SEGCODE field has "C", write "C8"
Else just copy anything from "TYPE" field into "NEWTYPE".

I used Nested IIf and it did not quite work.
NEWTYPE: IIf([SEG_CODE]="A","A1 " , (IIf([SEG_CODE]="B","B3 ",
(IIf[SEG_CODE]="C","C8" , Format([SEG_CODE], "@;"))))

 

[Graham R Seach]

Jay,

MsgBox SEGCODE & IIf(SEGCODE="A",1, IIf(SEGCODE="B",3,8))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Van T. Dinh]

Try:

NEWTYPE: IIf([SEG_CODE]="A","A1 " ,
IIf([SEG_CODE]="B","B3 ",
IIf([SEG_CODE]="C","C8" , [TYPE])))

Type as one line in the Field row of the Query grid.

 

[John W. Vinson/MVP]

I have a field named "SEGCODE", in a query design view.
In a calculated field named "NEWSEGCODE", I need to write the following
expression.
If SEGCODE field has "A", write "A1"
Else If SEGCODE field has "B", write "B3"
Else If SEGCODE field has "C", write "C8"
Else just copy anything from "TYPE" field into "NEWTYPE".

An alternative to IIF() for this kind of thing is the Switch() function. It
takes arguments in pairs (basically any number of pairs), and reads them
left to right; if the first argment of the pair evaluates to True, it
returns the second member and quits. Hence:

NewSegCode: Switch([SegCode] = "A", "A1", [SegCode] = "B", "B3", [SegCode] =
"C", "C8", True, [Type])

John W. Vinson/MVP