Calculated Expressions with IIF Statement

[Tina Hudson]

I'm working on a calculated expression which contains
fields and strings. I want to know if there is an easier
way to do what I want to do.

This is what I want: "For the Creative Arts & Sciences
Magnet Schools (Bugg and Douglas)....."

Where: "Creative Arts & Sciences Magnet" is a field
[DisplayName], and (Bugg and Douglas) are the names of 2
schools. DisplayName is the name of a school model.

I would prefer having a query that tells me the names of
the schools for each school model and places all schools
for a particular model in parentheses after the model
name. But I can't seem to do that with a query.

What I thought might do it, but would be time consuming,
would be to include an IIF statement in the string
expression, such as:

="For the " & [DisplayName] & " " & IIf([Model]
=1,"(Root)", IIf [Model] = 2, "(Lincoln Heights)", IIf
[Model] = 3,"(Bugg and Douglas)" ....

And so forth, until all models have been assigned
schools. I don't even know if the IIF statement is
written correctly, so if not, please correct. However,
I'd love to know if there is a simpler way to accomplish
this.

Thanks so much for any help!

Tina Hudson

 

[Gary Walter]

I'm working on a calculated expression which contains
fields and strings. I want to know if there is an easier
way to do what I want to do.

This is what I want: "For the Creative Arts & Sciences
Magnet Schools (Bugg and Douglas)....."

Where: "Creative Arts & Sciences Magnet" is a field
[DisplayName], and (Bugg and Douglas) are the names of 2
schools. DisplayName is the name of a school model.

I would prefer having a query that tells me the names of
the schools for each school model and places all schools
for a particular model in parentheses after the model
name. But I can't seem to do that with a query.

What I thought might do it, but would be time consuming,
would be to include an IIF statement in the string
expression, such as:

="For the " & [DisplayName] & " " & IIf([Model]
=1,"(Root)", IIf [Model] = 2, "(Lincoln Heights)", IIf
[Model] = 3,"(Bugg and Douglas)" ....

And so forth, until all models have been assigned
schools. I don't even know if the IIF statement is
written correctly, so if not, please correct. However,
I'd love to know if there is a simpler way to accomplish
this.

Hi Tina,

It looks to me like you need to create
a lookup table

tblLookUp
Model School
1 Root
2 Lincoln Heights
3 Bugg and Douglas
.....

Then INNER JOIN tblLookUp
to your original table ON Model.

So calculated field would look like

"For the " & [DisplayName] & "(" & tblLookUp.School & ")"

Please respond back if I have misunderstood.

Good luck,

Gary Walter