Nest IIf

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

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

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
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.
 
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
 
Back
Top