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!

amz

"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
IIF:

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
prefer).
--

John W. Vinson [MVP]
.

.