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
|
|||
|
|||
IIf statement w/calc based on another field
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 a 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, |
#2
|
|||
|
|||
IIf statement w/calc based on another field
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]) On Mon, 20 Feb 2006 19:17:27 -0800, "lucyzoe" wrote: 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 a 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, -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Using variable in a get field content statement (Access2000) | TheUbe | Using Forms | 7 | June 14th, 2005 05:18 PM |
Field that populates based on other field | NewIdiot | General Discussion | 1 | April 7th, 2005 10:15 PM |
Select field based on variables | Christopher King | Running & Setting Up Queries | 1 | February 23rd, 2005 02:10 PM |
Filling a field based on whether another field is filled/empty | Susan L | Using Forms | 4 | September 15th, 2004 04:05 PM |
Create table with field names based on the value of a field in another table. | [email protected] | Database Design | 5 | May 24th, 2004 02:32 PM |