OK.

The issue with the Case( not working was localization - I had to use a semi colon instead of a period.

But now, I have another issue - I'm building an online shopping cart and I have four databases - customers, carts, products, and orderitems.

Orderitems is the database that the customer sees their shopping cart through - when a customer adds a product to the shopping cart, the product price is looked up and placed into the orderitems file. The quanity the user sets for this item is a field that directly resides in orderitems.

My issue is this: In the end, I want to have lower prices for higher quanitities. I have three fields - quantity, price, and name. I want to have a formula that basically says this:

If the product is "watch", qty is < or equal to 10, then the price should be $10. If this qty is >10 then the price should be $20.

I know how to use the Case ( for strictly dynamic total based on a quantity field, but can it work for quantity and a name field?

I really hope someone can help with this.

Thanks,

Chris

I suggest that you keep set up a product/qty/price file rather than using a case function. This will give you more versatility. In your order file you can make a calculated field called ProductQty which is the concatenation of the product ID and the Quantity, like so:

ProductID & ":" & Right("00000" & Qty,5)

If the ProductID is "Watch" and Qty is 15, then the result of the calculation will be "Watch:00015"

Then you can set up a Price file that contains a record for each combination of price and quantity for each product. Like this:

ProductID, MinQty, Price, ProductQty

-------------------------

Watch, 0, 20.00, Watch:00000

Watch, 20, 10.00, Watch:00020

Bathtub, 0, 120.00, Bathtub:00000

Bathtub, 200, 89.95, Bathtub:00200

etc.

Now all you have to do is set up a relationship between the OrderItems file and the price file based on the ProductQty fields. Then set the price field in the OrderItems file to autoenter looked up value from the price file and select the "copy next lower" option. This will then automatically pick the correct price for a given product and quantity. Plus, you can have as many quantity discounts for a product as you want.

By the way, I highly recommend that you use a stock number or other unique ID number like "wtch0018" rather than a descriptive word like "Watch" to identify a product. Otherwise, what happens if you start to sell two different kinds of watches?