Nested IIF Functions

[krymer]

I need to use a nested IIF function to display Freshman, Sophomore, Junior,
or Senior for my corresponding class codes in the database FR, SO, JR, and
SR. I have this entered into the expression builder so far:

IIF([Students]![FR],"Freshman", etc...

However, I have been told that the two digit code for freshman needs to be
in quotation marks, not brackets. I tried doing this, but Access gave me
error messages. How wold the expression look for each class? Thanks for your
help!

 

[Ken Sheridan]

The Correct syntax would be:

IIF([Students].[ClassCode] = "FR","Freshman", IIF([Students].[ClassCode] =
"SO","Sophomore",……..

However, this is not a good way of doing it. For one thing the calling the
IIF function repeatedly is inefficient in performance terms. More
importantly, however, it goes against one of the fundamental principals of
the database relational model, which is that data is stored as values at
column positions in rows in tables and in no other way. Freshman, Sophomore
etc are data values, so returning them via the IIF function means that the
data is being hard-coded in the query, and thus violating the above rule –
its known as the Information Rule and was Codd's Rule 1 when he first put
forward the relational model for databases back in 1970.

The correct approach is to have a ClassCodes table with columns ClassCode,
and ClassName, the former with FR, SO etc the latter with Freshman, Sophomore
etc. So the table would have four rows.

All you need to do now is include the ClassCodes table in the query, joined
to Students on ClassCode and return the ClassName column.

One thing you should do when creating a relationship between ClassCodes and
Students is enforce cascade updates. This means that should you ever change
a class code in ClassCodes the changes will be automatically applied to
matching rows in Students.

Storing data only as values in tables does of course mean that it is
user-updatable, whereas hard-coding it requires an amendment to the
application design. Should the end user prefer to use the gender-unspecific
term Fresher rather than Freshman for instance, all that's necessary is to
update one row in the ClassCodes table.

Ken Sheridan
Stafford, England

 

[Thomas Hardy]

If you absolutely wanted to use a formula rather than a ClassCode table then
the Switch function would be more appropriate than IIF:

Class:
Switch([Students].[ClassCode]="FR","Freshman",[Students].[ClassCode]="SO","Sophomore",[Students].[ClassCode]="JR";"Junior",[Students].[ClassCode]="SR","Senior")

This is better because you only need one Swtich statement instead of
multiple (and nested) IIFs.

The ClassCode table is by far the best solution however.

 

[John Spencer]

Untested assumption on my part. But nested IIF might actually be faster
then using the switch function. Switch is a VBA function. IIF is a VBA
function but in queries IIF is part of the JET SQL language and
therefore it might actually be faster to call IIF several times then it
would be to call the VBA Switch function one time.

On the other hand there may be no practical difference (especially with
smaller recordsets and especially if not sorting or filtering were involved)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

If you absolutely wanted to use a formula rather than a ClassCode table then
the Switch function would be more appropriate than IIF:

Class:
Switch([Students].[ClassCode]="FR","Freshman",[Students].[ClassCode]="SO","Sophomore",[Students].[ClassCode]="JR";"Junior",[Students].[ClassCode]="SR","Senior")

This is better because you only need one Swtich statement instead of
multiple (and nested) IIFs.

The ClassCode table is by far the best solution however.

I need to use a nested IIF function to display Freshman, Sophomore, Junior,
or Senior for my corresponding class codes in the database FR, SO, JR, and
SR. I have this entered into the expression builder so far:

IIF([Students]![FR],"Freshman", etc...

However, I have been told that the two digit code for freshman needs to be
in quotation marks, not brackets. I tried doing this, but Access gave me
error messages. How wold the expression look for each class? Thanks for
your
help!

 

[Thomas Hardy]

Interesting observation, I didn't know that.

I suppose there is still the advantage with Switch that the layout is more
intuitive than the nested IIF but that might be less important if the
process ends up being slower.

Thomas

Untested assumption on my part. But nested IIF might actually be faster
then using the switch function. Switch is a VBA function. IIF is a VBA
function but in queries IIF is part of the JET SQL language and therefore
it might actually be faster to call IIF several times then it would be to
call the VBA Switch function one time.

On the other hand there may be no practical difference (especially with
smaller recordsets and especially if not sorting or filtering were
involved)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

If you absolutely wanted to use a formula rather than a ClassCode table
then the Switch function would be more appropriate than IIF:

Class:
Switch([Students].[ClassCode]="FR","Freshman",[Students].[ClassCode]="SO","Sophomore",[Students].[ClassCode]="JR";"Junior",[Students].[ClassCode]="SR","Senior")

This is better because you only need one Swtich statement instead of
multiple (and nested) IIFs.

The ClassCode table is by far the best solution however.

I need to use a nested IIF function to display Freshman, Sophomore,
Junior,
or Senior for my corresponding class codes in the database FR, SO, JR,
and
SR. I have this entered into the expression builder so far:

IIF([Students]![FR],"Freshman", etc...

However, I have been told that the two digit code for freshman needs to
be
in quotation marks, not brackets. I tried doing this, but Access gave me
error messages. How wold the expression look for each class? Thanks for
your
help!