Syntax for If then Conditional Statement
in [Queries]
|
Prev: top N per employee
Next: Time/Date problem
From: Melissa on 27 Jun 2008 10:21 I'm trying to write a conditional statement in a query that gives me the following: If product ID = 64, 128, or 75, then the renewal rate is 1%. All other product ID's get a renewal rate of 2%. My statement below gives me 1% for all products, and I can't get the syntax right to give me the 2% value for all other products. Anyone have any suggestions? Renewal: IIf([Hoppenstein]![ProductID]="64" Or "128" Or "75",[Hoppenstein]![Renewal]*0.01,[Hoppenstein]![Renewal]*0.02) -- Thanks, Melissa
From: KARL DEWEY on 27 Jun 2008 10:31 Use this --- Renewal: IIf([Hoppenstein]![ProductID]="64" Or [Hoppenstein]![ProductID]="128" Or [Hoppenstein]![ProductID]="75",[Hoppenstein]![Renewal]*0.01,[Hoppenstein]![Renewal]*0.02) If ProductID field is not text but is a number field then remove the quotes like this -- Renewal: IIf([Hoppenstein]![ProductID]=64 Or [Hoppenstein]![ProductID]=128 Or [Hoppenstein]![ProductID]=75,[Hoppenstein]![Renewal]*0.01,[Hoppenstein]![Renewal]*0.02) -- KARL DEWEY Build a little - Test a little "Melissa" wrote: > I'm trying to write a conditional statement in a query that gives me the > following: > If product ID = 64, 128, or 75, then the renewal rate is 1%. All other > product ID's get a renewal rate of 2%. My statement below gives me 1% for all > products, and I can't get the syntax right to give me the 2% value for all > other products. Anyone have any suggestions? > > Renewal: IIf([Hoppenstein]![ProductID]="64" Or "128" Or > "75",[Hoppenstein]![Renewal]*0.01,[Hoppenstein]![Renewal]*0.02) > -- > Thanks, > Melissa
From: Marshall Barton on 27 Jun 2008 10:55 Melissa wrote: >I'm trying to write a conditional statement in a query that gives me the >following: >If product ID = 64, 128, or 75, then the renewal rate is 1%. All other >product ID's get a renewal rate of 2%. My statement below gives me 1% for all >products, and I can't get the syntax right to give me the 2% value for all >other products. Anyone have any suggestions? > >Renewal: IIf([Hoppenstein]![ProductID]="64" Or "128" Or >"75",[Hoppenstein]![Renewal]*0.01,[Hoppenstein]![Renewal]*0.02) Try something more like: Renewal: [Hoppenstein]![Renewal] * IIf([Hoppenstein]![ProductID]="64" Or [Hoppenstein]![ProductID]="128" Or [Hoppenstein]![ProductID]="75",0.01,0.02) Or, more concisely: Renewal: Hoppenstein!Renewal * IIf(Hoppenstein!ProductID IN("64","128"."75"), 0.01, 0.02) That presumes that ProductID is a Text field. If it is a numeric type field, then lose the quotes. -- Marsh MVP [MS Access]
From: Duane Hookom on 27 Jun 2008 11:09 I would probably manage this data rather hard-coded values in expressions in queries. What happens when you want to add ProductID 96 or remove 75? Do you want to find and maintain expressions burried in queries? http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx If you don't want to store the 1% and 2% in tables, a compromise would be to create a very simple user defined function that accepts the ProductID and returns the percent. Save this function in a module with a name like "modBusinessCalcs". -- Duane Hookom Microsoft Access MVP "Melissa" wrote: > I'm trying to write a conditional statement in a query that gives me the > following: > If product ID = 64, 128, or 75, then the renewal rate is 1%. All other > product ID's get a renewal rate of 2%. My statement below gives me 1% for all > products, and I can't get the syntax right to give me the 2% value for all > other products. Anyone have any suggestions? > > Renewal: IIf([Hoppenstein]![ProductID]="64" Or "128" Or > "75",[Hoppenstein]![Renewal]*0.01,[Hoppenstein]![Renewal]*0.02) > -- > Thanks, > Melissa
From: Melissa on 27 Jun 2008 15:22
Worked like a charm! -- Thanks, Melissa "Marshall Barton" wrote: > Melissa wrote: > > >I'm trying to write a conditional statement in a query that gives me the > >following: > >If product ID = 64, 128, or 75, then the renewal rate is 1%. All other > >product ID's get a renewal rate of 2%. My statement below gives me 1% for all > >products, and I can't get the syntax right to give me the 2% value for all > >other products. Anyone have any suggestions? > > > >Renewal: IIf([Hoppenstein]![ProductID]="64" Or "128" Or > >"75",[Hoppenstein]![Renewal]*0.01,[Hoppenstein]![Renewal]*0.02) > > > Try something more like: > > Renewal: [Hoppenstein]![Renewal] * > IIf([Hoppenstein]![ProductID]="64" Or > [Hoppenstein]![ProductID]="128" Or > [Hoppenstein]![ProductID]="75",0.01,0.02) > > Or, more concisely: > > Renewal: Hoppenstein!Renewal * IIf(Hoppenstein!ProductID > IN("64","128"."75"), 0.01, 0.02) > > That presumes that ProductID is a Text field. If it is a > numeric type field, then lose the quotes. > > -- > Marsh > MVP [MS Access] > |