Mutiple IIF Statements

[Heather]

Hi,

I am not sure how to do this I need an if that will produce the following

iif([FIELD] = "A", "Proj1", iif "B", "Proj2", iif "C", "Proj3", iif "D",
"Proj4"))

Can anyone tell me how to formatt a mutiple iif statement?

Thanks!

 

[vanderghast]

Swith( field= "A", "Proj1", field="B", "Proj2", field="C", "Proj3",
field="D", "Proj4", true, "Other" )

which includes a safe guard/ catch all: if the test fail for all previous
cases, "Other" will be returned. You have to type the full test, each time,
not just ="B", or "B", as example,for the third argument, but field="B".


Vanderghast, Access MVP

 

[John Spencer MVP]

Something like the following

IIF(Field="A","proj1", IIF(Field="B","ProjB", IIF(Field="C","Proj3",
IIF(Field="D","Proj4",NULL))))

As al alternative you can use the SWITCH function which uses pairs of
arguments - first argument is the test, second argument is the return value.

SWITCH(Field="A","proj1", Field="B","ProjB", Field="C","Proj3",
Field="D","Proj4", True,NULL)

Best solution would be to have a table (ConversionTable) that has the
equivalents (two fields - MatchField with A, B etc. and ProjectName with the
names of your projects) in it and join that table to an existing table and
field in your query. The SQL for that would look something like:

SELECT ConversionTable.ProjectName, YourTable.FieldA, YourTable.FieldB
FROM YourTable LEFT JOIN ConversionTable
ON YourTable.Field = ConversionTable.MatchField



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Klatuu]

Just for grins, here is another way:
Nz(Choose(Asc(Ucase(Nz([FIELD],0)))-64,"Proj1","Proj2","Proj3","Proj4"),"Other")

 

[vanderghast]

Should be SWITCH, not SWITH, sorry for the typo.

Vanderghast, Access MVP