Nest IIf

J

Jay

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], "@;"))))
 
G

Graham R Seach

Jay,

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

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
V

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.
 
J

John W. Vinson/MVP

Jay said:
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".
Click to expand...

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
 
Top