Re: IIF stmt in query

John: thanks for the switch statement. It now works! I was not familar with
this type of function so thanks so much for sharing it with me. Thanks too
to all the other posts. Very helpful!


"John W. Vinson" wrote:

On Mon, 1 Mar 2010 14:41:01 -0800, AMZ <AMZ@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Thanks, but i'm not sure i was clear in my question. I have one field
(TransType) that has the following data A, B, T, E and another field (EType)
that goes with the E in the TransType that contains R or U. Here is what i
want to return for each option:

For A return FMYHF0002
For B return FMYHF0003
For T return FMYHF0005
For E with an etype of R return FMYHF0004
For E with an etype of U return FMYHF0006

I'd suggest using two calls to the Switch function - sort of a multibranch

Switch([TransType] = "A", "FMYHF0002", [TransType] = "B", "FMYHF0003",
[TransType] = "T", "FMYHF0005", [TransType] = "E", Switch([EType] = "R",
"FMYHF0004", [EType] = "U", "FMYHF0006", True, "Error in input"), True, "Error
in input")

The Switch function takes arguments in pairs, and evaluates them left to
right; when it first finds a pair for which the first element is TRUE it
returns the second element. Therefore I added a "catch" condition last - if it
gets through all the pairs and NONE of them match, it will return "Error in
Input" (which you can change to a more useful message or to NULL if you

John W. Vinson [MVP]