IIf statement w/calc based on another field

L

lucyzoe

I've inherited a MS Access product database with item options. I'm trying to
create a price list/catalog based on the product database:

The fields in the Query are:
A. Item_Price (The price of the item if no options are given)
B. Price_Calc (The pricecalc field has several values 1=Add, 2=Subtract,
3=None, 4=Use Item Price)
C. Option_Price (The amount you alter the Item_Price based on Price_Calc)
D. Adj_Item_Price (The price of the item with options)

I need to calculate the price; however, I don't know how to use the
Price_Calc field. Here's what I'm trying to get to:
IIf ([price_calc]=1, [item_price]+[option_price]=[Adj_Item_Price], OR
IIf ([price_calc]=2, [item_price]-[option_price]=[Adj_Item_Price],, OR
IIf ([price_calc]=3, [display item_price], OR
IIf ([price_calc]=4, [display item_price]

Any suggestions?

Thank you,
 
J

John Nurick

Hi LucyZoe,

Choose() is simpler here than a series of nested IIf()s. A calculated
field somewhat like this in the query should do the job:

Adj_Item_Price: Choose([PriceCalc], [item_price]+[option_price],
[item_price]-[option_price], [item_price], [item_price])



I've inherited a MS Access product database with item options. I'm trying to
create a price list/catalog based on the product database:

The fields in the Query are:
A. Item_Price (The price of the item if no options are given)
B. Price_Calc (The pricecalc field has several values 1=Add, 2=Subtract,
3=None, 4=Use Item Price)
C. Option_Price (The amount you alter the Item_Price based on Price_Calc)
D. Adj_Item_Price (The price of the item with options)

I need to calculate the price; however, I don't know how to use the
Price_Calc field. Here's what I'm trying to get to:
IIf ([price_calc]=1, [item_price]+[option_price]=[Adj_Item_Price], OR
IIf ([price_calc]=2, [item_price]-[option_price]=[Adj_Item_Price],, OR
IIf ([price_calc]=3, [display item_price], OR
IIf ([price_calc]=4, [display item_price]

Any suggestions?

Thank you,
Click to expand...
 
Top