IIf function

K

Kali

Does anyone know how I can make this If statement simplier? I want it to
display 4 if the cells shows "acquistion" etc... and otherwise blank.

IIf([TRANS_TYPE]="ACQUISITION",4,IIf([TRANS_TYPE]="ADDITIONS",1,IIf([TRANS_TYPE]="DEPR EXPENSE",2,"")))
 
J

Jeff Boyce

(note - Access doesn't have "cells")

IIF([Trans_Type] = "Acquisition", 4, "")

By the way, consider creating a table of valid [Trans_Type] values, and
their corresponding (whatever '4' is). Then you won't have to locate all
your IIF() statements when things change!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
T

tkelley via AccessMonster.com

Try this ....

Take advantage of the relational model to avoid this complex evaluation.
Build a table with two fields:

Trans_Type
Trans_TypeNum

Then you can do an LEFT join to that table in your query, add [Trans_TypeNum]
to the query. The LEFT join would leave it null if there was no match.

That would also make further expansion much easier than having to add it to
the IIf statement.

So unless I'm missing something, that may work for you.
Does anyone know how I can make this If statement simplier? I want it to
display 4 if the cells shows "acquistion" etc... and otherwise blank.

IIf([TRANS_TYPE]="ACQUISITION",4,IIf([TRANS_TYPE]="ADDITIONS",1,IIf([TRANS_TYPE]="DEPR EXPENSE",2,"")))
Click to expand...
 
C

Clifford Bass

Hi Kali,

Definitely a code table as Jeff mentioned. However, without a code
table you can use the Switch() function.

Switch([TRANS_TYPE]="ACQUISITION",4,[TRANS_TYPE]="ADDITIONS",1,[TRANS_TYPE]="DEPR EXPENSE",2,true,"")

Clifford Bass
 
Top