IFF Statement with Multiple conditions

[cacolon]

HI All.

I'm trying to create an IIF statement to calculate a Price on an item given certain conditions on ACCESS. For example:

TotalCost: IIF([ColorType]="B/W" and IIF([Machine]= "Ricoh 3510" or "Ricoh 8200-1" or "Ricoh 8220-1" or "Ricoh 8220-2" then [UnitsRequested]*0.0039,0))


I don'y know if my sintaxis is OK. I have an Idea but Don't know if I'm right.

Can you help?

Thanks!!!!!!

 

[plog]

Its both syntatically and logically incorrect. First, you don't need that second, nested IIf--remove it and its associated parenthesis. Also, remove the 'then' and replace it with a comma.

Then logically, you need to explicitly state every comparison, you can't just assume an operator and operands. If you want to compare A to 3 other things this is incorrect:

If A=B Or C OR D...

You need to explicitly compare C and D to A.

If A=B OR A=C OR A=D ...

 

[shakey]

Dont know all the facts but it seems arithmetically you have the Brackets in wrong place
Try this
IIF([ColorType]="B/W" and IIF([Machine]= "Ricoh 3510" or "Ricoh 8200-1" or "Ricoh 8220-1" or "Ricoh 8220-2" then [UnitsRequested]*0.0039),0)

 

[shakey]

Or this

IIF([ColorType]="B/W" and[Machine]= "Ricoh 3510" or "Ricoh 8200-1" or "Ricoh 8220-1" or "Ricoh 8220-2", [UnitsRequested]*0.0039,0)

This should do the calculation if conditions are met or return 0 for any other colour OR machine

.....I think

 

[CJ_London]

try

TotalCost: IIF([ColorType]="B/W" and [Machine] IN ("Ricoh 3510","Ricoh 8200-1","Ricoh 8220-1","Ricoh 8220-2"),[UnitsRequested]*0.0039,0)

 

[Galaxiom]

Requiring a statement like that indicates your data structure has real problems. You are hard coding data. What are you going to do when it changes? Edit the code?

Data should be in tables and retrieved directly through the joins in queries.