Calculated field with incremental costs

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have 2 forms
FrmProductCost
Product 1-10 boxes - 11-20 boxes- 21-30 boxes- Minimum Order Valu
A $2.50 $2.25 $2.00 $20.0
B et

FrmInvoiceCharg
Cust Product boxes Cos

In the cost field I would like to calculate that if the cust orders 7 boxes of Product A the minumum $20.00 kicks in. Also how do I phrase the expr to look up the amount of boxes to return the cost per box

Thank

Geron
 
Instead of FrmProductCosts, create a table with fields:
ProductID foreign key to Product.ProductID
Qty Number minimum
CostEach Currency

Enter records like this:
A 1 $2.50
A 11 $2.25
A 21 $2.00
B 1 $9.00
B 11 $9.00
B ...


You can then lookup the price in your order form with something like this:
DLookup("CostEach", "tblProductCost",
"(ProductID = " & Nz([ProductID], 0) & ") AND (Qty >= " & Nz([Qty], 0) &
")")

Since the Minimum Order Value seems to be related to the product, place that
in the Product table.

More help on DLookup():
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Geron said:
I have 2 forms:
FrmProductCosts
Product 1-10 boxes - 11-20 boxes- 21-30 boxes- Minimum Order Value
A $2.50 $2.25 $2.00 $20.00
B etc

FrmInvoiceCharge
Cust Product boxes Cost


In the cost field I would like to calculate that if the cust orders 7
Click to expand...
boxes of Product A the minumum $20.00 kicks in. Also how do I phrase the
expr to look up the amount of boxes to return the cost per box?
 
Back
Top