|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 27th, 2008, 03:21 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Syntax for If then Conditional Statement
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 
|
|
#2
June 27th, 2008, 03:31 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Syntax for If then Conditional Statement
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
|
|
#3
June 27th, 2008, 03:55 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Syntax for If then Conditional Statement
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]
|
|
#4
June 27th, 2008, 04:09 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Syntax for If then Conditional Statement
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/arc...2/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
|
|
#5
June 27th, 2008, 08:22 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Syntax for If then Conditional Statement
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]
|
|
#6
June 27th, 2008, 08:24 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Syntax for If then Conditional Statement
I agree about finding expressions buried in queries. This might be a bit
beyond my talents, but I like a challenge, so I'll try it. -- Thanks, Melissa "Duane Hookom" wrote: 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/arc...2/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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
