Can I use multiple "IIF" statements?

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

 

[Norman Yuan]

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

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

 

[John Vinson]

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]

 

[Q Johnson]

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

 

[John Vinson]

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]