Multiple IIf function in design view

[2Plan]

I would like to create a make-table that uses a multiple IIf function similar to the statement below:

DivAbr: IIf([BLOCKNAME]="640-10","UTFRI",[DIVISIONABBR])

Based on the 8 different [BLOCKNAME], I want the [DIVISIONABBR] to say "UTFRI".

It works for one condition, but not for several. Does anyone know how to get this to work?

Thanks

 

[MajP]

I think I would simply build a small table

tblBlockNames
strName

In this table put your 8 names: "640-10"

Now left join your table to tblBlockNames and include strName in the query. The ones that do not "link" will be null

iff(isnull([strName]),[DivisionABBR],"UTFRI")

Makes it easy to change or add more without changing the sql.

 

[2Plan]

This was very helpful!

Thanks.

 

[CautionMP]

FYI: multiple [tt]IIf[/tt]'s = [tt]Switch()[/tt] function.

 

[2Plan]

What would the syntax of the Switch function look like with a couple of block names in the example below?

DivAbr: IIf([BLOCKNAME]="640-10","UTFRI",[DIVISIONABBR])

Thanks.

 

[MajP]

http://www.techonthenet.com/access/functions/advanced/switch.php

 

[CautionMP]

The last argument is your catch all, just needs to be any expression that evaluates True (since False = 0 you should be able to use any other integer, positive or negative.)
[tt]DivAbr: SWITCH(([BLOCKNAME]="640-10", "UTFRI", [BLOCKNAME]="740-10", "Something", [BLOCKNAME]="840-10", "SomethingElse", 1=1, [DIVISIONABBR])[/tt]

 

[2Plan]

Nice!

Thanks.