Iif Function

[loquinn]

I am trying to enter an If statement. Can you please help me with this
statement for Price per acre:

‘IIf([MitigationBank]="LCPMB",(“17500")),(IIf([MitigationBank]="WBJMB",("15000")))'

If the bank is LCPMB the price per acre is 17500 if it is WBJMB it is 15000.
In my query i have created a Calculated field to equal AcresPurchased *
PricePerAcre
I keep getting a syntax error.

 

[fredg]

I am trying to enter an If statement. Can you please help me with this
statement for Price per acre:

¡¥IIf([MitigationBank]="LCPMB",(¡§17500")),(IIf([MitigationBank]="WBJMB",("15000")))'

If the bank is LCPMB the price per acre is 17500 if it is WBJMB it is 15000.
In my query i have created a Calculated field to equal AcresPurchased *
PricePerAcre
I keep getting a syntax error.

In a query?
What if [MitigationBank] is neither "LCPMB" or 'WBJMB"?

If only those 2 values are possible....

Cost:IIf([MitigationBank]="LCPMB",17500 * [AcresPurchased],15000 *
[AcresPurchased])

If other criteria values are possible....

Cost:IIf([MitigationBank]="LCPMB",17500 *
[AcresPurchased],IIf([MitigationBank]="WBJMB",15000 *
[AcresPurchased], SomeOtherNumberValue * [AcresPurchased]))

 

[Wayne-I-M]

Something:
IIf([TableName]![MitigationBank]="LCPMB",17500,IIf([TableName]![MitigationBank]="WBJMB",1500))

This is not the best way to do this has you may end up with lots of banks.

 

[Beetle]

If LCPMB and WBJMB are the only two choices, then this
should work;

PricePerAcre: IIf([MitigationBank]= "LCPMB", 17500, 15000)

with an additional field for the total;

Total: [AcresPurchased]*[PricePerAcre]

 

[John W. Vinson]

I am trying to enter an If statement. Can you please help me with this
statement for Price per acre:

‘IIf([MitigationBank]="LCPMB",(“17500")),(IIf([MitigationBank]="WBJMB",("15000")))'

If the bank is LCPMB the price per acre is 17500 if it is WBJMB it is 15000.
In my query i have created a Calculated field to equal AcresPurchased *
PricePerAcre
I keep getting a syntax error.

IIF takes three arguments: a logical expression, a value if the expression is
TRUE, and an expression if the value is FALSE. What do you want to be the
price if MitigationBank is NULL, or some value other than LCPMB or WBJMB?

If PurchasePrice is a Currency value you don't want the quotes, and in any
case you don't need all these parentheses. Try

TotalPrice: [AcresPurchased] * IIF([MitigationBank] = "LCPMB", 17500,
IIF([MitigationBank] = "PBJMB", 15000, NULL))

if you want the sale value to be NULL if neither condition holds. If there are
more than two or three MitigationBank values, I'd not use IIF at all; instead,
store the price and the MitigationBank values in a table and just join that
table.

 

[Duane Hookom]

IMO, you shouldn't even be using an expression like this. This is data and
should be stored in a table. I would never rely on 17500 or 15000 not
changing over time. Your banks will probably change. I would have a field in
a table of banks (or some other table) that store 17500 and 15000. You should
maintain data, not expressions tucked away in queries or control sources.

 

[loquinn]

Thank you - this did the trick. I knew just enough to be dangerous. Have a
great day.
--
loquinn

I am trying to enter an If statement. Can you please help me with this
statement for Price per acre:

‘IIf([MitigationBank]="LCPMB",(“17500")),(IIf([MitigationBank]="WBJMB",("15000")))'

If the bank is LCPMB the price per acre is 17500 if it is WBJMB it is 15000.
In my query i have created a Calculated field to equal AcresPurchased *
PricePerAcre
I keep getting a syntax error.

In a query?
What if [MitigationBank] is neither "LCPMB" or 'WBJMB"?

If only those 2 values are possible....

Cost:IIf([MitigationBank]="LCPMB",17500 * [AcresPurchased],15000 *
[AcresPurchased])

If other criteria values are possible....

Cost:IIf([MitigationBank]="LCPMB",17500 *
[AcresPurchased],IIf([MitigationBank]="WBJMB",15000 *
[AcresPurchased], SomeOtherNumberValue * [AcresPurchased]))