calculated textbox

[angie]

i have a report based on a query with many fields among which are:
"gross price" (euro field) and discount zone fields as following: "africa";
"europe"; "special" (number fields e.g. 40,30, etc.).

i have a form that contains a combo that the user can select discount zones
(africa, europe and special). i want to create a calculated field that
performs the following calculation: gross price * (1- [discount zone field
selected by user in the form]/100).

can i achieve something like that?

 

[Allen Browne]

Since one product can have multiple prices depending on zones, the way to do
this in a relational database is with 3 tables:

tblProduct table (you probably already have this), with fields:
- ProductID primary key
- ProductName text
- Price Currency (base value in euros.)

tblZone table, with a field:
- ZoneID primary key (can be text if you like, e.g. Africa)

tblPrice table, with fields:
- ProductID relates to an entry in tblProduct
- ZoneID relates to an entry in tblZone
- Multiplier Number (size Double) to multiple the price by.

The Multiplier can default to 1.
Less than 1 is a discount.
More than 1 is a higher price.

You can then use DLookup() to lookup the multiplier for a product in a zone,
like this:
DLookup("Multiplier", "tblPrice", "(ProductID = " & Nz([ProductID],0) & ")
AND (ZoneID = """ & [ZoneID] & """)")

For help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html