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]
.

.



Relevant Pages

  • Re: Do you protect your power switch with a lock?
    ... Of McKown, John ... Do you protect your power switch with a lock? ...
    (bit.listserv.ibm-main)
  • Re: Headless machine?
    ... John wrote: ... > been using this machine to download large music videos etc from the likes ... I used a KVM switch with the linux box and my main machine ...
    (comp.os.linux.networking)
  • Re: Bally 17 series, will not advance test modes....
    ... John, without rushing off to conjure up a Bally Schematic, I'll just ... take a wild guess and say that J3 pin 1 is where the test switch ties ... checking the coindoor connector. ... when I ground the J3 pin 1 connector on the MPU it will advance thru all the ...
    (rec.games.pinball)
  • Re: Tech: Gottlieb Sys 1 (Totem) Still Crazy
    ... Got our board back from Ace last week and finally got a chance to install it tonight. ... Thank you John and everyone else who hung in here through this mess. ... When you say you've been 'through the score switches' did you disconnect EVERY SINGLE switch and then try only hooking one up a time to see if the problem is present on each individual switch...one and only one switch is connected for each test. ... I suspect electrical noise is doing this, I know you put new diodes on the coils in question, but I'd really have to have the game in my shop to check this further. ...
    (rec.games.pinball)
  • Re: Switch
    ... Bill H wrote: ... that the switch didn't work at all. ... the comment that starts on the Define the mailto address line. ... the type of $etype is string. ...
    (comp.lang.php)