IIF Multiple Criteria

[D Willett]

Hi Guys

My project can use two sets of numbers to achieve the same result other than one set of numbers is gathered from and automated source and the other set gathered from a manual input.
Preferably we would use the manual input results but humans as they are forget to input them.
The difference between the two sets are that they can be split differently across three categories.

Example:
AutomatedPaint = 2.3
AutomatedPanel = 0
AutomatedFit = 5.9

ManualPaint = 2.0
ManualPanel = 0.9
ManualFit = 5.0

I could quite easily resolve this using:
IIF([ManualPaint]>0,[ManualPaint])

But I will need to use multiple conditions in case one of the results = 0 and its just split differently.
So, does my syntax look correct using the following:?

IIF([ManualPaint]>0 Or ([ManualPanel]>0 Or ([ManualFit]>0,[ManualPaint])))
IIF([ManualPaint]>0 Or ([ManualPanel]>0 Or ([ManualFit]>0,[ManualPanel])))
IIF([ManualPaint]>0 Or ([ManualPanel]>0 Or ([ManualFit]>0,[ManualFit])))

Therefore if any of the manual inputs are greater than 0 the chances are that the inputter hasn't forgot, so use those results instead of the Automated versions, and if he has forgot then all three results will be 0 so use the Automated results instead.

[HansV]

I don't think the parentheses are placed correctly, I'd use

IIf([ManualPaint]>0 Or [ManualPanel]>0 Or [ManualFit]>0,[ManualPaint],[AutomatedPaint])

[D Willett]

Hi Hans, yes thats what I was unsure of. I'll test it :-)

Thanks Gain

[Mark L]

Try the Switch() function. It is often easier to use (and MUCH easier to read later) than IIF(), especially when there is a complicated situation.

[D Willett]

Thank you Mark.

The function IIF is working so its panic off so to speak. Hadn't heard of the switch function before, worth looking up though.

Thanks again.

[HansV]

The Switch function is a bit like the Select Case ... End Select construct in VBA. It is handy if you have multiple IIfs.

For example, the following expression

IIf([Amount]<=10,0,IIf([Amount]<=50,2,IIf([Amount]<=100,5,IIf([Amount]<=500,10,IIf([Amount]<=1000,25,50)))))

can be written using Switch:

Switch([Amount]<=10,0,[Amount]<=50,2,[Amount]<=100,5,[Amount]<=500,10,[Amount]<=1000,25,[Amount]>1000,50)

Although it is not shorter, it is more readable and less error-prone, since you don't have to count the parentheses.

[Mark L]

While everyone seems to know IIF(), the Switch() and Choose() functions are often overlooked but very useful. As Hans showed in his illustration, they are much easier to construct and much easier to read later; try to look at one of those convoluted IIF constructs a year from now (or for those of us getting a little older, next week)!