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.
IIF Multiple Criteria
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
IIF Multiple Criteria
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: IIF Multiple Criteria
I don't think the parentheses are placed correctly, I'd use
IIf([ManualPaint]>0 Or [ManualPanel]>0 Or [ManualFit]>0,[ManualPaint],[AutomatedPaint])
IIf([ManualPaint]>0 Or [ManualPanel]>0 Or [ManualFit]>0,[ManualPaint],[AutomatedPaint])
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: IIF Multiple Criteria
Hi Hans, yes thats what I was unsure of. I'll test it :-)
Thanks Gain
Thanks Gain
Cheers ...
Dave.
Dave.
-
- 3StarLounger
- Posts: 331
- Joined: 11 Feb 2010, 03:55
- Location: Land O Lakes, FL
Re: IIF Multiple Criteria
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.
Mark Liquorman
Land O Lakes, FL
see my website http://www.liquorman.net for Access Tips and Tricks, and for my Liquorman Utilities.
Land O Lakes, FL
see my website http://www.liquorman.net for Access Tips and Tricks, and for my Liquorman Utilities.
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: IIF Multiple Criteria
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.
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.
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: IIF Multiple Criteria
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.
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.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 331
- Joined: 11 Feb 2010, 03:55
- Location: Land O Lakes, FL
Re: IIF Multiple Criteria
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)!
Mark Liquorman
Land O Lakes, FL
see my website http://www.liquorman.net for Access Tips and Tricks, and for my Liquorman Utilities.
Land O Lakes, FL
see my website http://www.liquorman.net for Access Tips and Tricks, and for my Liquorman Utilities.