Calculation in Access

[Aibhlinn]

In my database I have a list price (our cost) and a unit price (retail
price). I also have a field for a discount that is taken against the unit
price to get the extended price.

Problem is, I need to come up with a calculation, or expression so when the
discount is entered on the form, that the discount does not make the extended
price, lower than the list price of each of the items. The list price is not
viewable to the user and we don't want it to be.

How the form is set up is a subform holding:
Product ID
Quantity
Unit Price
Discount
Extended Price

I am pulling the information from an extended query.

Any help will be grealy appreciated as the most simple way to keep the user
from entering to big of a discount and throwing up a msg box.

 

[Larry Daugherty]

Keep thinking about what it is that you want to do. You haven't fully
defined the desired outcome in terms that can be calculated.

As an analogy, think of Access as though it were your own private
lumber yard with every kind of lumber you could wish. Imagine, too,
that it includes a magnificent workshop with every machine tool and
hand tool you could wish. That's all *resource* that you can use in
constructing your solutions:

Build boats through elegant yachts
Build structures from humble sheds to magnificent houses and estates
Boils fences, walls and decorative structures.

All of those uses require that you (or someone) think through the
desired outcome and define it fully. Then use the resources at your
disposal (Access) to manifest that solution.

For the issue you posted, I have to assume that your sales people are
quoting prices on one item at a time customers. For that, I'd first
work out a general algorithm as to how to determine the lowest
allowable piece price on any item. It would have to be your unit cost
plus some "j" factor. The calculation could be more complex depending
on price thresholds, etc. As soon as you select an item for quoting,
a text box (a label might be getter) on the user's form should display
that lowest legal value for that item and it should be labeled "Lowest
unit Price". The unit price your user offers will be entered into a
different textbox on the form. In the event that some lower price is
selected, there should be a warning with a message box notifying the
user that their suggested price is too low.

HTH