Creating a field based on another column using Switch or IIF?

[lsetla1]

I tried the following:

Expr1: Switch([High_School]='Davis High School' Or [High_School]='West Valley
High School',‘Yakima’,[High_School]='Tonasket High School',’Tonasket’,True,
[’’])

....but something is wrong. I am allowed to run the syntax but I am asked for
parameter values for Yakima, Tonasket, True, and "". When I run the query,
Expr1 displays no results.

Any help would be greatly appreciated! Thanks!!

 

[KARL DEWEY]

Try this --
Expr1: Switch([High_School]="Davis High School", "Yakima",
[High_School]="West Valley High School","Yakima", [High_School]= "Tonasket
High School", "Tonasket")

 

[lsetla1 via AccessMonster.com]

ahhh! I knew it was something simple! Thanks a lot Karl!

Try this --
Expr1: Switch([High_School]="Davis High School", "Yakima",
[High_School]="West Valley High School","Yakima", [High_School]= "Tonasket
High School", "Tonasket")

I tried the following:

[quoted text clipped - 7 lines]

Any help would be greatly appreciated! Thanks!!

 

[lsetla1 via AccessMonster.com]

Hrm...one last thing. When I added the rest of my arguments in (about 20 or
so all together) ACCESS gave me an error of 'expression too complex'. I
couldn't find an upper limit for arguments in the switch function. Is there
one?

This was the syntax I used:

Expr1: Switch([High_School]="Davis High School","Yakima",[High_School]="West
Valley High School","Yakima",[High_School]="Kittitas High School","Kittitas",
[High_School]= “Mariner High Schoolâ€, “Everettâ€, [High_School]= “Truman
Centerâ€, “Federal Wayâ€, [High_School]= “Kent-Meridian High Schoolâ€, “Kentâ€,
[High_School]= “Clover Park High Schoolâ€, “Lakewoodâ€, [High_School]= “Mabton
High Schoolâ€, “Mabtonâ€,
[High_School]= “Cleveland High Schoolâ€, “Seattleâ€, [High_School]= “Stevenson
High Schoolâ€, “Stevensonâ€, [High_School]= “Henry Foss High Schoolâ€, “Tacomaâ€,

[High_School]= “Lincoln High Schoolâ€, “Tacomaâ€, [High_School]= “Mount Tahoma
High Schoolâ€, “Tacomaâ€, [High_School]= “Tonasket High Schoolâ€, “Tonasketâ€,
[High_School]= “Foster Senior High Schoolâ€, “Tukwilaâ€, [High_School]= “Yelm
High Schoolâ€, “Yelmâ€);

Thanks Again

Switch([High_School]="Davis High School","Yakima",[High_School]="West Valley
High School","Yakima",[High_School]=“Mariner High Schoolâ€, “Everettâ€)

ahhh! I knew it was something simple! Thanks a lot Karl!

Try this --
Expr1: Switch([High_School]="Davis High School", "Yakima",

[quoted text clipped - 6 lines]

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It would probably be a better idea to use a lookup table and JOIN it to
your query to produce the county/city names for the schools. E.g.:

Table: SchoolLocations

Columns: school_nbr location
---------- ----------
101 Yakima
102 Yakima
201 Everett

Then, in the query, just JOIN the SchoolLocations table to your other
table:

SELECT S.school_name, SL.location
FROM Schools As S INNER JOIN SchoolLocations As SL
ON S.school_nbr = SL.school_nbr
WHERE ....

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSqk/9IechKqOuFEgEQLUkwCg5qRLyn7/9yhe7bE4YB25GEDUfZwAoJn3
17BtCvEKxW4fQTHkranzWSv0
=x630
-----END PGP SIGNATURE-----

Hrm...one last thing. When I added the rest of my arguments in (about 20 or
so all together) ACCESS gave me an error of 'expression too complex'. I
couldn't find an upper limit for arguments in the switch function. Is there
one?

This was the syntax I used:

Expr1: Switch([High_School]="Davis High School","Yakima",[High_School]="West
Valley High School","Yakima",[High_School]="Kittitas High School","Kittitas",
[High_School]= “Mariner High Schoolâ€, “Everettâ€, [High_School]= “Truman
Centerâ€, “Federal Wayâ€, [High_School]= “Kent-Meridian High Schoolâ€, “Kentâ€,
[High_School]= “Clover Park High Schoolâ€, “Lakewoodâ€, [High_School]= “Mabton
High Schoolâ€, “Mabtonâ€,
[High_School]= “Cleveland High Schoolâ€, “Seattleâ€, [High_School]= “Stevenson
High Schoolâ€, “Stevensonâ€, [High_School]= “Henry Foss High Schoolâ€, “Tacomaâ€,

[High_School]= “Lincoln High Schoolâ€, “Tacomaâ€, [High_School]= “Mount Tahoma
High Schoolâ€, “Tacomaâ€, [High_School]= “Tonasket High Schoolâ€, “Tonasketâ€,
[High_School]= “Foster Senior High Schoolâ€, “Tukwilaâ€, [High_School]= “Yelm
High Schoolâ€, “Yelmâ€);

Thanks Again

Switch([High_School]="Davis High School","Yakima",[High_School]="West Valley
High School","Yakima",[High_School]=“Mariner High Schoolâ€, “Everettâ€)

ahhh! I knew it was something simple! Thanks a lot Karl!

Try this --
Expr1: Switch([High_School]="Davis High School", "Yakima",

[quoted text clipped - 6 lines]

Any help would be greatly appreciated! Thanks!!