IIf, then..else - nesting three statements

[Jeff Monroe]

I have a query that contains a field called [loccode]. I am filterin
data as follows:"61 or 62", "68 or 69" and "between 71 and 79".

Each filter criteria equals a department area:
"61 or 62" = Dept Area A; "68 or 69" = Dept Area B and "71 through 79
= Dept Area C.

I want to populate field [Dept Area] with the three items listed above
as per the [loccode] criteia.

My individual iif statements are as follows:
Dept Area: IIf([loccode] = "61" Or "62","Dept A","")
Dept Area: IIf([loccode] = "68" Or "69","Dept B","")
Dept Area: IIf([loccode] Between "71" And "79","Dept C","")

They work fine individually in their own queries, but I want them i
one query. I've tried different ways of nesting the statements, bu
only get errors.

Thanks for any help offered.

Jef

 

[Rob Parker]

Hi Jeff,

First, I'm surprised that you say "They work fine individually in their own
queries ..."; to get the first two to work you need something like:
Dept Area: IIf([loccode]="61" Or [loccode]="62","Dept A")
or
Dept Area: IIf([loccode] In (61,62),"Dept A")

Then you nest your IIf statements, thus:
Dept Area: IIf([loccode] In (61,62),"Dept A",IIf([loccode] In
(68,69),"Dept B",IIf([loccode] Between 71 And 79,"Dept C","")))

You could also use an In construct for the last IIf:
IIf([loccode] In (71 to 79),"Dept C","")

HTH,

Rob

 

[[MVP] S.Clark]

How about creating a lookup table, and linking it in the query?


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[Jeff Monroe]

Hey Rob:

Your solution worked out for me.

Thanks for the help.

Jeff

Hi Jeff,

First, I'm surprised that you say "They work fine individually in their
own
queries ..."; to get the first two to work you need something like:
Dept Area: IIf([loccode]="61" Or [loccode]="62","Dept A")
or
Dept Area: IIf([loccode] In (61,62),"Dept A")

Then you nest your IIf statements, thus:
Dept Area: IIf([loccode] In (61,62),"Dept A",IIf([loccode] In
(68,69),"Dept B",IIf([loccode] Between 71 And 79,"Dept C","")))

You could also use an In construct for the last IIf:
IIf([loccode] In (71 to 79),"Dept C","")

HTH,

Rob


"Jeff Monroe" (e-mail address removed) wrote in
message
I have a query that contains a field called [loccode]. I am filtering
data as follows:"61 or 62", "68 or 69" and "between 71 and 79".

Each filter criteria equals a department area:
"61 or 62" = Dept Area A; "68 or 69" = Dept Area B and "71 through
79"
= Dept Area C.

I want to populate field [Dept Area] with the three items listed
above,
as per the [loccode] criteia.

My individual iif statements are as follows:

They work fine individually in their own queries, but I want them in
one query. I've tried different ways of nesting the statements, but
only get errors.

Thanks for any help offered.

Jeff