Syntax for If then Conditional Statement

[Melissa]

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)

 

[KARL DEWEY]

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)

 

[Marshall Barton]

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.

 

[Duane Hookom]

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".

 

[Melissa]

Worked like a charm!
--
Thanks,
Melissa

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.

 

[Melissa]

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

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

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)