Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Can I use multiple "IIF" statements?

Discussion in 'Information Technology' started by Dez, Jul 28, 2006.

  1. Dez

    Dez
    Expand Collapse
    Guest

    I'm trying to make a calculation that would work something like the following:

    If code = XXX, then multiply cost times XXX rate
    If code = YYY, then multiply cost times YYY rate
    If code = ZZZ, then multiply cost times ZZZ rate

    Any ideas as to how I can accomplish this calculation?

    Thanks...
     
  2. Loading...


  3. Norman Yuan

    Norman Yuan
    Expand Collapse
    Guest

    Of course you can use Iif() inside Iif(), such as:

    Something=cost * Iif (code=xxxx, AAA, Iif(code=yyyy,BBB,CCC))


    "Dez" <Dez@discussions.microsoft.com> wrote in message
    news:62AAC9BE-FC92-4050-B5DE-CD698FC3EDF5@microsoft.com...
    > I'm trying to make a calculation that would work something like the
    > following:
    >
    > If code = XXX, then multiply cost times XXX rate
    > If code = YYY, then multiply cost times YYY rate
    > If code = ZZZ, then multiply cost times ZZZ rate
    >
    > Any ideas as to how I can accomplish this calculation?
    >
    > Thanks...
     
  4. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Mon, 10 Jul 2006 12:29:02 -0700, Dez
    <Dez@discussions.microsoft.com> wrote:

    >I'm trying to make a calculation that would work something like the following:
    >
    >If code = XXX, then multiply cost times XXX rate
    >If code = YYY, then multiply cost times YYY rate
    >If code = ZZZ, then multiply cost times ZZZ rate
    >
    >Any ideas as to how I can accomplish this calculation?
    >
    >Thanks...


    Nested IIF() is, of course, possible - but it would be fairly far down
    my list of choices.

    Since you're using a relational database, you might want to consider a
    relational solution: a table Rates with two fields, Code and Rate. You
    could then simply join this table in a Query, linking by Code, and use
    an expression

    NewCost: [Cost] * [Rate]

    The Switch() function would be another option... but a table-based
    solution would give you the simplest and most easily maintained
    solution!

    John W. Vinson[MVP]
     
  5. Q Johnson

    Q Johnson
    Expand Collapse
    Guest

    WOW! John Vinson?

    There's a blast from the past!! I don't think I've seen your name anywhere
    I've been since AOL blew the crap out of CompuServe years ago.

    How've you been?

    Regards,

    Q Johnson
     
  6. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Mon, 10 Jul 2006 20:16:02 -0500, "Q Johnson" <QJohnson@TeamNFP.com>
    wrote:

    >WOW! John Vinson?
    >
    >There's a blast from the past!! I don't think I've seen your name anywhere
    >I've been since AOL blew the crap out of CompuServe years ago.
    >
    >How've you been?
    >
    >Regards,
    >
    >Q Johnson
    >


    Hi Q. Good to see you over here! Yep, I gave up my account on
    Compuserve - it's still there and gets some traffic, but I just
    couldn't put up with the AOLization.

    I'm helping my wife with her farmers' market business, doing some
    consulting, and spending entirely too much time following newsgroups
    here...

    How about you?

    John W. Vinson[MVP]
     

Share This Page