joining two iif statements
in [Queries]
|
Prev: Recalculate a field when another field's value changes - JCW
Next: SQL BETWEEN If end date is less than start date all records betweenare still displayed.
From: Marc on 2 Apr 2010 20:34 I would like to combine two iif together. I am able to get the results from a single iif statement but not successful in combining the two. iif([cert master]!Verbiage Like "*Best Buy Gift Card*",0.008,[discount]/3*7) iif ([cert master]!Verbiage Like "*Bath & Body Works*",0.005, [discount]/3*7) I thought a comma would work, but it does not. If I try or it does not calculate correctly. Thank you
From: John W. Vinson on 2 Apr 2010 22:47 On Fri, 2 Apr 2010 17:34:01 -0700, Marc <Marc(a)discussions.microsoft.com> wrote: >I would like to combine two iif together. I am able to get the results from >a single iif statement but not successful in combining the two. > >iif([cert master]!Verbiage Like "*Best Buy Gift Card*",0.008,[discount]/3*7) > >iif ([cert master]!Verbiage Like "*Bath & Body Works*",0.005, [discount]/3*7) > >I thought a comma would work, but it does not. If I try or it does not >calculate correctly. > >Thank you So you want to use 3/7ths of the discount if the verbiage doesn't match either option? If so you need one IIF nested inside another, or, perhaps better, the builtin Switch() function. The latter takes arguments in pairs, evaluates the pairs left to right, and when it finds a true value in the first element returns the second and quits. Try Switch(([cert master]!Verbiage Like "*Best Buy Gift Card*" ,0.008, [cert master]!Verbiage Like "*Bath & Body Works*",0.005, True, [discount]/3*7) You can also use nested IIF's but that can get hard to read and hard to maintain. -- John W. Vinson [MVP]
From: John W. Vinson on 2 Apr 2010 23:15
On Fri, 02 Apr 2010 20:47:06 -0600, John W. Vinson <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote: >On Fri, 2 Apr 2010 17:34:01 -0700, Marc <Marc(a)discussions.microsoft.com> >wrote: > >>I would like to combine two iif together. I am able to get the results from >>a single iif statement but not successful in combining the two. >> >>iif([cert master]!Verbiage Like "*Best Buy Gift Card*",0.008,[discount]/3*7) >> >>iif ([cert master]!Verbiage Like "*Bath & Body Works*",0.005, [discount]/3*7) >> >>I thought a comma would work, but it does not. If I try or it does not >>calculate correctly. >> >>Thank you > >So you want to use 3/7ths of the discount if the verbiage doesn't match either >option? > >If so you need one IIF nested inside another, or, perhaps better, the builtin >Switch() function. The latter takes arguments in pairs, evaluates the pairs >left to right, and when it finds a true value in the first element returns the >second and quits. Try > >Switch(([cert master]!Verbiage Like "*Best Buy Gift Card*" ,0.008, >[cert master]!Verbiage Like "*Bath & Body Works*",0.005, >True, [discount]/3*7) Sorry - had an extra ( in there, should be Switch([cert master]!Verbiage Like "*Best Buy Gift Card*" ,0.008, [cert master]!Verbiage Like "*Bath & Body Works*",0.005, True, [discount]/3*7) -- John W. Vinson [MVP] |