Mutiple IIF Statements

  • Thread starter Thread starter Heather
  • Start date Start date
H

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!
 
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
 
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
 
Just for grins, here is another way:
Nz(Choose(Asc(Ucase(Nz([FIELD],0)))-64,"Proj1","Proj2","Proj3","Proj4"),"Other")
 
Back
Top